[SOLVED] Copying charts/data

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

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


"Paul Reeve" wrote:

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

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

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

Paul

"Paul Reeve" <[email protected]> wrote in message
news:%[email protected]...
> 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
>

Thanks, John, that worked a treat

Paul

"John Mansfield" <[email protected]> wrote in message
news:[email protected]...
> 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 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
>>
>>
>>