Copying charts/data

  • Thread starter Thread starter Paul Reeve
  • Start date Start date
P

Paul Reeve

Hi all,

I apologise if this has been asked before: I could not find any reference.

I have a workbook with about 50 sheets on which I need to plot the same
range of cells (Q49:T206) on an embedded chart in each sheet showing the
different data in the sheets.
If I just copy the chart & data then paste it into each of the other sheets,
it references the original data uniquely.

Is there a simple way of doing what I need with vba? Or do I have to change
all 4 series on each sheet manually?

thanks in advance for any help

remove the obvious if replying

Paul
 
Paul,

Assuming you already have one embedded chart per sheet, try the following
code. It uses a counter (the variable -> Cnt) to refer to the sheet number.
The counter is incremented by 1 as each sheet is looped through e.g. Sheet1,
Sheet2, Sheet3, Sheet4,..., Sheet50 etc. The actual names on the sheet tabs
shouldn't matter. Place the code in a standard module to run it.

Sub ChangeSource()
Dim wks As Worksheet
Dim cht As ChartObject
Cnt = 1
For Each wks In Worksheets
For Each cht In wks.ChartObjects
cht.Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=Sheets(Cnt).Range("Q49:T206")
Next cht
Cnt = Cnt + 1
Next wks
End Sub
 
Paul -

A quick alternative: Copy the old sheet with the data and embedded chart. Copy the
new data, and paste it into the new sheet on top of the old data.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Thanks, John, that worked a treat

Paul

John Mansfield said:
Paul,

Assuming you already have one embedded chart per sheet, try the following
code. It uses a counter (the variable -> Cnt) to refer to the sheet
number.
The counter is incremented by 1 as each sheet is looped through e.g.
Sheet1,
Sheet2, Sheet3, Sheet4,..., Sheet50 etc. The actual names on the sheet
tabs
shouldn't matter. Place the code in a standard module to run it.

Sub ChangeSource()
Dim wks As Worksheet
Dim cht As ChartObject
Cnt = 1
For Each wks In Worksheets
For Each cht In wks.ChartObjects
cht.Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=Sheets(Cnt).Range("Q49:T206")
Next cht
Cnt = Cnt + 1
Next wks
End Sub
Click to expand...
 
Back
Top