Hi all,

I apologise if this has been asked befo 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

----
Regards,
John Mansfield
http://www.pdbook.com


"Paul Reeve" wrote:

Hi all,

I apologise if this has been asked befo 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 -

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

Paul Reeve wrote:

Hi all,

I apologise if this has been asked befo 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

thanks gents - I will try these out when I get a chance

Paul

"Paul Reeve" wrote in message
...
Hi all,

I apologise if this has been asked befo 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

Thanks, John, that worked a treat

Paul

"John Mansfield" wrote in message
...
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

----
Regards,
John Mansfield
http://www.pdbook.com


"Paul Reeve" wrote:

Hi all,

I apologise if this has been asked befo 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