Update Chart data Range with VBA

[Roger]

Hi,

I do have approx 50 charts (with multiple series) in 1 workbook (excel
97) which show monthly data. I would like to update the chart data
range to show always to the last 12 months of data by running once a
month a macro.

I would like to do with VBA and not with named ranges (as the latter
means that I have to re-setup all graphs with named ranges). To do
this I would like to read-out the current XValues and Values of the
SeriesCollection(i) (see below) into a string or range and brake up
the string and alter this to a new range and update the
Seriescollection again.

I have listed below part of the loop to update all charts; but I the
code does not accept the tempString or tempRange as declared below.
However I can set the XValues and Value properties in VBA (also shown
below)

How should I proceed?


Dim tempString As String
Dim tempRange As Range

ActiveSheet.ChartObjects("Chart 15").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.ChartArea.Select
tempString = ActiveChart.SeriesCollection(1).XValues
tempRange = ActiveChart.SeriesCollection(1).XValues



ActiveChart.SeriesCollection(1).XValues = "=Datasheet!R4C19:R4C31"
ActiveChart.SeriesCollection(1).Values =
"=Datasheet!R59C19:R59C31"


Thanks for any help,
Roger

 

[Don Guillett]

I would still recommend a dynamic name such as last12 on the sheet
=offset($a$1,counta($a:$a)-12,0,12,1)
or use a sub to do it for you
Sub setname()
ActiveWorkbook.Names.Add Name:="Last12", RefersTo:= _
"=OFFSET(ThisWorks!$a$1,COUNTA(ThisWorks!$a:$a)-12,0,12,1)"
End Sub

and use this just ONCE to set the series for you without activation

Sub setnewseries()
Sheets("yoursheet").ChartObjects("Chart 1").Chart _
.SeriesCollection(1).Values = "=yourworkbookname.xls!Last12"
End Sub

Should be automatic from now on.