duplicating charts in a workbook

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello
I'd like to create the same chart (i.e., from the same range of data), on each worksheet in a workbook. Is there a quick way to do this? (like a batch chart function?)
 
Turn on the macro recorder and record the steps as you create one chart.
Then, delete that chart and modify the code to loop through all the
sheets --

At the top of the code, add the following lines:

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets

Just before the End Sub line, add:

Next ws

Also, change any reference from a specific sheet, to the variable 'ws'.
For example, instead of:

ActiveChart.SetSourceData _
Source:=Sheets("Sheet1").Range("A1:G6"), _
PlotBy:=xlRows
ActiveChart.Location Where:=xlLocationAsObject, _
Name:="Sheet1"

use:

ActiveChart.SetSourceData _
Source:=ws.Range("A1:G6"), _
PlotBy:=xlRows
ActiveChart.Location Where:=xlLocationAsObject, _
Name:=ws.Name

Run the revised macro, and it should create a chart on each sheet.
 
Another way, if the data is structured the same on all the sheets, is to
make a chart on one sheet, and format it the way it should appear
everywhere. Make as many copies of this sheet (with the chart on it) as
you need. Then one sheet at a time, copy the other data, and paste it
over the original values on the copied sheets.

Using recorded macros to create charts is okay, but they are not
efficient, and they require a fair bit of work to streamline. I use
them all the time, but I also use techniques like the one I describe above.

- Jon
 
I'm unfamiliar with macros, but learning..
For a line of code such as
ActiveChart.SeriesCollection(3).Name = "='ER1308'!R12C2

where "ER1308" is the name of the worksheet, and I would like to have that refer to all worksheets (depending on where the macro is in its work), what code should I insert here

Thank you.
 
Back
Top