Macro to change Chart Range when inserting a column

[Guest]

I have created a workbook to use as a weekly reporting tool for different
sites across the country. It contains a "Report" sheet and a "Data" sheet.
There are 4 charts embedded into the Report sheet. I have set-up the Data
sheet to have the data titles in Column A, a 12-week summary in Column B, and
the weekly data begins at Column C. For the 12-week, I used the formulas
=sum($C$1:$P$1) and on the Chart series, I used =Data!$C$1:$P$1. I designed
the sheet so a center would insert a column at Column C each week for the
most recent data. Unfortunately, everytime they insert the column, the
formulas do stay static to Column C, instead they change to $D$1:$P$1.
Question 1: Is there a way to make the formulas in both the 12-week summary
and the Charts stay at columns C and not change everytime a column gets
inserted?
Since I couldn't get that to work yet on my own, I tried to create a macro
that would change all of the formulas back and then recreate the chart and
place it in the correct location on the "Report" sheet. I get an error
everytime it runs. It will change the formauls, but it won't format the chart
properly and instead stops the macro with a "Run-time error 1004: Unable to
get the ChartObjects property of the worksheet class."
Question2: If thre isn't a way to make the Cell ranges static, how can I
replace existing charts with new ones using a macro so every center can
simply run the same macro without needing to make any manual changes to the
charts?

Thanks for any guidance you can provide!

 

[Jon Peltier]

Mark -

You don't need a macro. Try making a dynamic chart, using defined range
names that don't keep moving. For example, to define $C$1:$P$1 when
someone might insert a new column before the existing column C, try a
range name like this. On the Insert menu, select Name > Define. Enter a
name like rngC1P1 in the name box, and in refers to, enter this formula:

=OFFSET($B$1,0,1,1,14)

which means (using the arguments left to right) define the range which
relative to $B$1, starts zero rows down and one column right, is one row
high and 14 columns wide. As long as $B$1 isn't changed by row or column
insertions, you're cool.

when making the chart, you have to define the ranges for each series
separately. In step 2 of the chart wizard, or on the Source Data dialog,
go to the Series tab. In place of =Sheet1!$C$1:$P$1 in the range
selection boxes, enter =Sheet1!rngC1P1.

More examples and links:

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______