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 |
All times are GMT +1. The time now is 11:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com