Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Copying charts/data
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|