Bug: Move or Copy sheet graph links not updating!

[Guest]

This is very frustrating! I spent a week preparing a worksheet with a lot of
graphs on the same single sheet. I need to do the same for 20 different
departments. In previous versions of Excel (e.g. 2003) I just could copy the
prepared worksheet via 'Move of Copy' (rightclick on the tab) and all would
be fine. In Excel 2007 however, all graphs still link to data ranges on the
original sheet.

Example:
Sheet called "Total" with a graph with data series ='Total'!$E$15:$E$36.
Copy sheet to a new tab called "Dep1". In Excel 2003 the data series would
automatically be changed to ='Dep1'!$E$15:$E$36. In 2007 however, it is still
='Total'!$E$15:$E$36.

Please please please, is there any solution besides manually changing
hunderds of data series? Starting again in Excel 2003 is no option either.

Alice

 

[Bernard Liengme]

Hi Alice,
A quick experiment showed that XL2007 behaves the same way as XL2003
I made a chart on Sheet1; I used the right click method to make a copy (you
can also hold down CTRL and drag the worksheet's tab).

The new chart on Sheet1 (2) displays this in the Formula Bar:
=SERIES('Sheet1 (2)'!$B$1,'Sheet1 (2)'!$A$2:$A$6,'Sheet1 (2)'!$B$2:$B$6,1)
But if I rename Sheet 1(2) as Sheet 2 then I get
=SERIES(Sheet2!$B$1,Sheet2!$A$2:$A$6,Sheet2!$B$2:$B$6,1)
Furthermore, clicking the chart puts range finders on the 'home' sheet so I
know the reference has gone to the newly created sheet.
Have a cuppa-tea and try again
best wishes

 

[Guest]

From 2007 save it in 2003 format. Then read it into 2003?????

 

[Guest]

Joel, Bernard,

Thanks for the replies. The tea didn't help and Excel 2003 is no option
either since all nice formatting I incorporated in my graphs. I mean, who
wants to go back to dull 2003 graphs?

Do you know what is so strange? That from all graphs that are on my sheet,
there are two that DO update, the rest does not. I've spent more than an hour
trying to find a reason why this is so.

Anyway, I am experimenting to create a macro that will do the work for me.
That way, I ony have to specifie the ranges only once, and the rest is for
Excel to do.

Sub Macro1()
'
' Macro1 Macro
'

'
Dim strSheetName As String

strSheetName = ActiveSheet.Name
ActiveSheet.ChartObjects("Higlights pie").Activate
ActiveChart.SeriesCollection(1).Values = "='" & strSheetName &
"'!$E$58:$E$60"
ActiveSheet.ChartObjects("History bar").Activate
ActiveChart.SeriesCollection(1).Name = "='" & strSheetName & "'!$D$14"
ActiveChart.SeriesCollection(1).Values = "='" & strSheetName &
"'!$E$15:$E$36"
ActiveChart.SeriesCollection(2).Name = "='" & strSheetName &
"'!$F$14:$G$14"
ActiveChart.SeriesCollection(2).Values = "='" & strSheetName &
"'!$F$15:$F$36"
ActiveChart.SeriesCollection(3).Name = "='" & strSheetName & "'!$F$106"
ActiveChart.SeriesCollection(3).Values = "='" & strSheetName &
"'!$F$107:$F$128"
ActiveChart.SeriesCollection(4).Name = "='" & strSheetName & "'!$G$106"
ActiveChart.SeriesCollection(4).Values = "='" & strSheetName &
"'!$G$107:$G$128"
' etc etc
End Sub

 

[CC]

This is very frustrating! I spent a week preparing a worksheet with a lot of
graphs on the same single sheet. I need to do the same for 20 different
departments. In previous versions of Excel (e.g. 2003) I just could copy the
prepared worksheet via 'Move of Copy' (rightclick on the tab) and all would
be fine. In Excel 2007 however, all graphs still link to data ranges on the
original sheet.

Example:
Sheet called "Total" with a graph with data series ='Total'!$E$15:$E$36.
Copy sheet to a new tab called "Dep1". In Excel 2003 the data series would
automatically be changed to ='Dep1'!$E$15:$E$36. In 2007 however, it is still
='Total'!$E$15:$E$36.

Please please please, is there any solution besides manually changing
hunderds of data series? Starting again in Excel 2003 is no option either.

Alice

I can confirm that this is indeed a bug or flaw in Excel 2007. As
Alice stated, Excel 2003 would correctly update the links when copying
to point to the data on the copied sheet. Excel 2007 instead
continues to point to the old sheet for the chart data. Maybe this
has something to do with the type of chart or the fact that the
workbook was created in 2003 and subsequently saved to 2007?