Copying a chart to different sheets

Microsoft Office 2007 home and student
July 12, 2010 at 05:38:06
Specs: Windows XP

I want to copy a chart to every sheet I have, but when I do this the chart just take the data from the sheet where I copied it from. What I want it to do is to update the data it so it takes it from the sheet where I paste it in.

All my data is in the same cells in each sheet. I hope somebody can help me. It would save me for a lot of time! Thanks! :)
I have very many sheets so I can't update the chart so it takes the right data in every sheet manually.

See More: Copying a chart to different sheets

Report •

July 12, 2010 at 07:35:32

Here is a macro that will change all the series in all the Charts, so that each chart series points to the worksheet the chart is in.

As this is a one-off process I suggest that the code is run manually - no point in creating a button to run it from

Create your first chart and copy and paste it to the other worksheets.

Make a backup copy of your work and test this on the backup - changes made by Macros cannot be undone with the undo function/button.

Now go back to the worksheet containing the original chart - it must be the worksheet where you created the chart (but in the workbook that you are testing this on)

Right-click on the worksheet name Tab
Select View Code.
In the Visual Basic window that opens, paste in this code:

Sub Chartset()
Dim wsEach As Worksheet
Dim strThisWs As String
Dim chEach As ChartObject
Dim strFmla As String

'get name of calling worksheet
strThisWs = ActiveSheet.Name

For Each wsEach In ActiveWorkbook.Worksheets()
    'exclude calling worksheet
    If wsEach.Name <> strThisWs Then
        'get each chart object on the worksheets
        If wsEach.ChartObjects.Count > 0 Then
            For Each chEach In wsEach.ChartObjects
                'go through each data series
                For n = 1 To chEach.Chart.SeriesCollection.Count
                    'replace original worksheet name with this worksheet's name
                    strFmla = chEach.Chart.SeriesCollection(n).Formula
                    chEach.Chart.SeriesCollection(n).Formula = _
                        Replace(strFmla, strThisWs, wsEach.Name)
                Next n
            Next chEach
        End If
    End If
Next wsEach
End Sub

Now click on the first line - Sub Chartset()
Click f8
Keep clicking f8 until the line
For Each wsEach In ActiveWorkbook.Worksheets()
is highlighted
Hover the mouse over the code "strThisWs" on the prior line
It should show the name of the worksheet that contained the original chart.
If not click Run - Reset and go back to the main Excel window and select a cell on the original chart's worksheet.
Use alt + f11 to go back to the code, and start again.

Assuming the name is correct this time, hit f5 and the rest of the code will run to completion.

Go back and check your charts.

Note that this code does not change any other chart links - for example if you linked the Chart title to a cell.
If you need to change anything other than the data series please let me know and I will see if I can add it.

This code has only been tested on limited data and has not been tested with your data or in your environment, so ensure that you have backup copies of your data.


Report •

July 12, 2010 at 23:25:55
Thanks a lot Humar! It works great. You saved my day :)

Report •

July 13, 2010 at 04:10:22

You're welcome.



Report •
Related Solutions

Ask Question