Named range in chart - any way to change the workbook reference with VBA

[Graham Whitehead]

Hi,

Here is my problem. I create an interactive report from a master workbook.
This uses charrts which are updated automatically when new data is read in.
So I am using named ranges on the charts. However, in the source data for
the chart is have to state the workbook name and the range, i.e.
='Master.xls'!NamedRange

However, when I run the code to hide all data etc from the user I save this
as a different name. Is there a way to change the source range for the
chart? Hope thanks makes and thank for any help.

 

[Jon Peltier]

You can change each of the XValues and Values:

ActiveChart.SeriesCollection(1).Values = "'" & mySheet.Name & "'!" &
myName

or the series formula:

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

- Jon