Update Chart data Range with VBA

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

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.

--
Don Guillett
SalesAid Software
[email protected]
"Roger" <[email protected]> wrote in message
news:[email protected]...
> 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