Same cell range on different sheet

[unit2436]

I have a workbook with many sheets containing the same type of data.
The user has made charts on one sheet and would like to copy them to
all of the other sheets, and have each chart reference the same area of
data on the new sheet, i.e. it should reference, say, A1:B20 on
whatever sheet it's on.

Is there a way to access individual parts of the series source in VBA?
I can write a procedure to retrieve the xvalues and values for the
series, but I don't know how to separate out the sheet name from the
range/cells reference.

If this can be done by hand, he'll be just as happy to copy and paste
the set of charts to each worksheet & have it re-link to the same area
of the new sheet. Ideally there will be a final product where he can
tinker with charts on one page and spend less than 5 minutes either
clicking an "update all sheets" button or copying the entire set of
charts to every page. I would go for a macro, but it must be something
he doesn't need to worry about, otherwise he'd rather find the way to
copy & paste.

Thanks for any insight.

 

[unit2436]

I found the function I needed on another site, thanks to Andy for his
posts there. The solution is Replace which searches a string for text
and replaces it with new text. I copied and pasted the charts then
searched the new chart formulas for the old sheet name & replaced it
with the new name. Here's the line I needed:

With Worksheets(sh).ChartObjects(i).Chart.SeriesCollection(1)
.Formula = Replace(.Formula, modelsheet, current)
End With

 

[Jon Peltier]

I wrote a utility to handle this:

http://peltiertech.com/Excel/Charts/ChgSrsFmla.html

- Jon