Copy chart from one worksheet to several

[excelactuary]

Hello,

I have a workbook with 12 sheets (named "january" to "december") which contain data blocks having the same structure. The first sheet ("january") has a chart with 4 lines referencing to data in the sheet. I want to copy this chart to the other 11 sheets and automatically adjust the data source for each sheet. How can this be done? I would appreciate a macro, because if I change the chart structure in the first sheet, then I could simply run the macro again.

Thank you!

[LJMetzger]

Hi excelactuary,

Working with Excel Charts always seems more challenging than it should be, unless the 'Chart Wizard' does what you want.

See the following code in Ordinary Code module ModChartCopy that is included in the attached sample file:

Please Login or Register  to view this content.

It is a best practice to declare all variables. If you misspell a variable in your code, VBA will silently assume it is a Variant variable and go on executing with no clue to you that you have a bug. Go to the VBA development window, click Tools, Options, and check "Require Variable Declaration." This will insert the following line at the top of all new modules:

Please Login or Register  to view this content.

This option requires all variables to be declared and will give a compiler error for undeclared variables.

Notes:
a. Charts for 'January' are created/deleted for Software Development purposes only.
b. You can have any number of Charts of any type on Sheet 'January'.
c. Two charts arbitrarily exist on Sheet 'January'.
d. Data for both Charts is arbitrarily in 'A24:D26 (must preexist on each sheet in the same range)'.
e. Each Sheet Name (formula) is arbitrarily stored in cell 'C20' (must preexist on each sheet in the same cell)'.
f. Chart Title formulas are arbitrarily in cells 'A21' and 'A22' (must preexist on each sheet in the same range)'.
g. Macro CopyAllChartsOnSheetJanuaryToSheetsFebruaryThruDecember() deletes existing Charts on Sheet 'February' thru 'December'
and then copies all charts to Sheets 'February' thru 'December' .
Formulas for the 'Title' and 'Data Series' are adjusted on Sheets 'February' thru 'December' .

Months 'May' thru 'December' purposely do not exist in this workbook.

Data for 'Harry' is different on all sheets to verify that reference changes work properly.


Lewis