MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Changing series reference in a macro


Posted by Adam T. Dickson on August 31, 2001 12:53 PM

I am writing a macro that among other things will automoatically change the range to which a particular graph looks from one sheet in a workbook to another in that same workbook. For instance, an existing graph has a range on the "template" sheet of A3:A30,E3:E30. I want the macro to change the graph to that same cell reference on sheet "01" instead of "template". I recorded the macro in Excel; this is what I got for the data reference code:

Sheets("Charts").Select
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).XValues "='01'!C4:C340,f4:f340"
ActiveChart.SeriesCollection(1).Values = "='01'!$F$4:$F$340"
ActiveChart.SeriesCollection(1).Name = "='01'!$E$2"

But Excel won't run this! As near as I can tell, .XValues, .Values, and .Name are read only properties.

Can anyone help me with this. I am stuck and my head is sore from beating it against the wall.


Posted by Mark W. on September 01, 2001 2:48 PM

Your XValues assignment doesn't appear to be proper.
Try using...

ActiveChart.SeriesCollection(1).XValues = "='01'!R4C3:R340C3" Sheets("Charts").Select ActiveSheet.ChartObjects("Chart 2").Activate ActiveChart.ChartArea.Select ActiveChart.SeriesCollection(1).XValues "='01'!C4:C340,f4:f340" ActiveChart.SeriesCollection(1).Values = "='01'!$F$4:$F$340" ActiveChart.SeriesCollection(1).Name = "='01'!$E$2"