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