Updating excel charts automatically each month with new data and rolling the last month off the end

Question

I have been given a set of historic data that requires line charting for the given range (say 12 months). We insert a new column at "B" each new month so the historic data moves along one column.

Can we automatically update the chart with the new months data and roll off the previous last month from the chart when we insert the new data?

We can change format the data to suit a solution ie possibly inserting a new column isn't the best way to add data, for the most part the the whole dashboard type solution is legacy - your thoughts appreciated

Tony B

Answer 1

You can do this in two different ways.

The first is with a VBA script which works by copying the data rather than inserting column B. When you insert a new column B, the chart range will follow the old data so that it's now using rows C through N (C:N) for the 12 months.

What you need to do is to have a VBA script which inserts a new column after all the current ones (let's say you had B:M and you're adding a new one) at N. This will not cause the chart range to be moved.

Then you simply copy all the data from M to N, L to M, K to L, ..., C to D and B to C, then blank out B. This leaves B free for your new data and the chart still using B:M.

The second way is to insert at column B but write the VBA to manipulate the chart object and force it to use B:M.

The chart object has a SetSourceData method for this very reason so you can change it with a simple macro (bound to a keypress or automatically on open, etc).

Answer 2

This is probably the easiest way to create a dynamic chart.

1. create a dynamically named range for the chart.
2. use the dynamic named range for your chart data range.

Here is an excellent tutorial by Jon Peltier

http://peltiertech.com/Excel/Charts/DynamicLast12.html

Chuck