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

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.

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

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Graham Whitehead" wrote in message
...
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.