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:P 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!
--
-Mark