Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Mark
 
Posts: n/a
Default Macro to change Chart Range when inserting a column

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
  #2   Report Post  
Jon Peltier
 
Posts: n/a
Default

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/
_______


Mark wrote:

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!

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Data Range Mess Karen Charts and Charting in Excel 18 January 14th 06 03:34 PM
Chart attached to dynamic range Avi Charts and Charting in Excel 1 August 8th 05 02:35 AM
Activating a Chart object Hari Prasadh Charts and Charting in Excel 6 August 2nd 05 07:22 PM
how to change range for dynamic chart in excel 2000 with button? ivan Charts and Charting in Excel 2 April 24th 05 04:10 AM
Chart DataSource Change jj Charts and Charting in Excel 3 January 20th 05 02:12 AM


All times are GMT +1. The time now is 05:20 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"