Same cell range on different sheet

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.

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


[email protected] wrote:
> 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.

I wrote a utility to handle this:

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


<[email protected]> wrote in message
news:[email protected]...
>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
>
>
> [email protected] wrote:
>> 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.
>