How do I change the source worksheet for 100 charts automatically.

[Excel Forecaster]

I have a worksheet with 100 charts that source a set of five worksheets,
financial forecast A. I create several forecasts (sets of 5 worksheets) and
copy over worksheet with 100 charts, but the charts reference financial
forecast A when I want them to source from financial forecast B. To change
the chart source to financial forecast B, I now have to open each chart and
change the source from A to B. Is there a way to do this automatically,
similar to the edit, find, replace function for data in cells?

 

[Andy Pope]

Hi,

You would need to write VBA code to do this.
Given you know what the value is you want to change you should be able
to change the series formula.

an example of a series formula could be,

=SERIES("Series Name",,'financial forecast A'!$E$1:$E$4,1)

so your code would be something like

activechart.SeriesCollection(1).formula = _
replace(activechart.SeriesCollection(1).formula, _
"financial forecast A", _
"financial forecast B")

Jon has some VBA coding for charts
http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

But if you need more help on the coding post back.

Cheers
Andy