Chart updates

[kiltubber]

Hi, I have a file that I use to chart some data. I update the data every week with new data from the previous week. I have the date, (ie each day) in the first colum and the next few colums contain the data for that day. When I update the data, I have to go to each chart and update the source data so that the chart will show the new data. I would like to automate this process using a little macro. One idea I had was to update a cell each time the data is added, with the row number that the data list extends to. The macro could use this to create a new source data address so that the chart could be updated. So the question is how do I create this: ActiveChart.SeriesCollection(1).Values = "='Historical Data'!R7C5:R140C5" using a macro. Thanks John.

 

[Swamp Thing]

You can set the chart's source data to refer to named ranges. For example, set the x and y sources of the chart to the names CurrentCategories and CurrentValues. Whenever you add new data, you can re-define these ranges.

The next level of automation is to define these ranges "dynamically" using formulas. Then by changing a single cell you can have all the charts pointing to the new data.
(NO Macros!)
For details, search the forum for dynamic ranges and dynamic charts; also check out http://www.tushar-mehta.com

 

[kiltubber]

Dynamic ranges it is, THANKS for the tip.
John