Thread
:
Update Chart data Range with VBA
View Single Post
#
2
Posted to microsoft.public.excel.charting,microsoft.public.excel.programming
Don Guillett[_4_]
external usenet poster
Posts: 2,337
Update Chart data Range with VBA
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
"Roger" wrote in message
m...
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
Reply With Quote
Don Guillett[_4_]
View Public Profile
Find all posts by Don Guillett[_4_]