duplicating charts in a workbook

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 Whe=xlLocationAsObject, _
Name:="Sheet1"

use:

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

Run the revised macro, and it should create a chart on each sheet.


H wrote:
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?)


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Debra Dalgleish wrote:

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 Whe=xlLocationAsObject, _
Name:="Sheet1"

use:

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

Run the revised macro, and it should create a chart on each sheet.


H wrote:

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?)