Updating series for all charts on Worksheet

[Elvis]

Hi,

I have the macro below that I've created to update chart ranges based on whenever new data/observations for the series become avaliable.

However I have several charts (20) on the worksheet which I need to do this for. How easy would it be to develop a macro to loop through each chart and update the series?

Is there a way to check what column in terms of the series each chartis referencing and then running a modified version of the code below based on that column? For example in the macro below, the seriescollection(1) is referencing column 28. Instead I'd like to loop through each chart, determine what column is being referenced for that chart and then update the chart range accordingly based on the last populated row found.

Thanks,

Elvis


[VBA]
Sub chartupdate3()
lastcell = Cells(1075, 28).End(xlDown).Row 'finds last populated cell
ActiveSheet.ChartObjects("Chart 17").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Values = Worksheets("Table").Range("AB979:AB" & lastcell)
ActiveChart.SeriesCollection(1).XValues = Worksheets("Table").Range("A979:A" & lastcell)
End Sub
[/VBA]

[mdmackillop]

Can you post a sample workbook? I don't thing we need all 20 charts though.

[Elvis]

Hello,

Please find attached an example. Here I've only included two charts (on sheet called Charts).

The reason I have 20 separate charts instead of one dynamic one (e.g using a v-lookup function etc) is because I like to print out all of the charts together so I can view them on paper and easily see what's happening across different series so I can see the full picture (like a dashboard). This is not easy to do when I'm just Looking at one chart at a time. Also at some point I'm looking to add more than one series to a chart.

Elvis

[mdmackillop]

How about using dynamic ranges as the source data.
http://peltiertech.com/WordPress/dyn...t-source-data/

[Elvis]

Hi,

Thanks for the suggestion. Just a quick question, If I went down the formula route, for an excel sheet with a minimum of 20 charts, would all the formulas slow down the workbook - calculation, opening closing etc.

I'd rather have a solution that least impacts teh productivity of the workbook.

Thanks,

Elvis

[mdmackillop]

I'm not a charting expert. Personally, I'd give it a try, then proceed based upon the results. What is acceptable performance depends upon your needs.