Update Chart data Range with VBA




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

How should I proceed?

Dim tempString As String
Dim tempRange As Range

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

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

Thanks for any help,



Don Guillett

I would still recommend a dynamic name such as last12 on the sheet
or use a sub to do it for you
Sub setname()
ActiveWorkbook.Names.Add Name:="Last12", RefersTo:= _
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.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question