Best approach to copy sheets with dynamic defined named ranges and charts.
- Thread starter looney
- Start date
SirJB7
Excel Rōnin
Hi, looney!
I'm afraid there's no easy way out.
Suppose you have 2 workbooks: A with 2 worksheets W1 & W2, where W1 has data and a chart, and W2 has the same chart but pointing to data in W1; B with a worksheet W0. If you copy W1 from A to B, you won't have any issue since W1 contains both the data and the chart, but if you copy W2 from A to B, when you open B being A closed you'd be prompted with the update source dialog since workbook B doesn't contains the data required for chart in worksheet W2.
Hope it helps.
Regards!
I'm afraid there's no easy way out.
Suppose you have 2 workbooks: A with 2 worksheets W1 & W2, where W1 has data and a chart, and W2 has the same chart but pointing to data in W1; B with a worksheet W0. If you copy W1 from A to B, you won't have any issue since W1 contains both the data and the chart, but if you copy W2 from A to B, when you open B being A closed you'd be prompted with the update source dialog since workbook B doesn't contains the data required for chart in worksheet W2.
Hope it helps.
Regards!
Hi,
I have indeed 2 workbooks and would like to copy a worksheet from workbook A to B. I guess the biggest problem is the defined names. The defined names in workbook A are dynamic (making use of the offset and count functions) and work fine in workbook A. The chart is on the same worksheet and linked to the defined names.
But when I copy the worksheet or some ranges it does seem to mess up the chart on the worksheet in workbook B.
I have indeed 2 workbooks and would like to copy a worksheet from workbook A to B. I guess the biggest problem is the defined names. The defined names in workbook A are dynamic (making use of the offset and count functions) and work fine in workbook A. The chart is on the same worksheet and linked to the defined names.
But when I copy the worksheet or some ranges it does seem to mess up the chart on the worksheet in workbook B.
Smallman
Excel Ninja
Hi Looney
This may be overly simplistic but whenever I have been faced with the same problem I move the sheet with the charts and the sheets with the data. I move them I don't copy them. Then I don't save the workbook I moved them from. This way the charts come across with the data and the appropriate links. So there are no external links in the new workbook and all the charts work as expected.
Take care
Smallman
This may be overly simplistic but whenever I have been faced with the same problem I move the sheet with the charts and the sheets with the data. I move them I don't copy them. Then I don't save the workbook I moved them from. This way the charts come across with the data and the appropriate links. So there are no external links in the new workbook and all the charts work as expected.
Take care
Smallman
Thanks Smallman!
It seems to do partly the trick. I am able to move sheet and keep all the settings.
Although this means I have delete every week all the other sheets when I perform the 'move' in VBA.
It will also create another problem. I am using this part of code on the sheet and it will cause an error.
This is the VBA behind the sheet. I tells the sheet what his name is from another sheet in a cell in workbook A
It seems to do partly the trick. I am able to move sheet and keep all the settings.
Although this means I have delete every week all the other sheets when I perform the 'move' in VBA.
It will also create another problem. I am using this part of code on the sheet and it will cause an error.
This is the VBA behind the sheet. I tells the sheet what his name is from another sheet in a cell in workbook A
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Name = Sheets("START").Range("C1").Value
End Sub
Last edited by a moderator:
jeffreyweir
Active Member
Here's a link that might be helpful:
http://peltiertech.com/WordPress/make-a-copied-chart-link-to-new-data/
http://peltiertech.com/WordPress/make-a-copied-chart-link-to-new-data/
I may have a solution. I had this same issue, and no one could seem to fix. Perhaps my named ranges are set up differently, but here's how I fixed. I have a bar graph that is connected to a form control that flips through the months so we can see a population % mix by month.
Go to Data, Edit Links
Find the link to the old workbook you copied from
Highlight and select 'Change Source'
From there, browse to your new workbook (with the copied data in it under the same tab name)
Select that file and click OK
My graph now updates as I flip through using the form control. This did not happen before. Maybe this is too simple, as my workbook is pretty simple, but I'm excited I got it working!
Go to Data, Edit Links
Find the link to the old workbook you copied from
Highlight and select 'Change Source'
From there, browse to your new workbook (with the copied data in it under the same tab name)
Select that file and click OK
My graph now updates as I flip through using the form control. This did not happen before. Maybe this is too simple, as my workbook is pretty simple, but I'm excited I got it working!