Copy a chart from one sheet to another using the target sheet data for the chart in Excel

Question

I have several worksheets which have different data but organized in the same way (same number of col and rows). I created several charts in sheet1 which I would like to copy to sheet2. while the normal copy/paste copies the charts to sheet2, the charts is still referring to data in sheet1, not in sheet2. How can I automatically make them use sheet2 data rather than sheet after copying?

As a work around, I tried copying sheet1 and called it sheet2 (which copies all data and charts), then copy and pasted the real sheet2 data in this new sheet. This works, but I was hoping there is a faster way and perhaps a macro that copies all charts from sheet1 to sheet2 and automatically updates the references.

Answer 1

The easiest way to copy a chart to another sheet and have the chart link to data on the new sheet, isn't to copy the chart. The easiest way is to copy the sheet including the chart, then change the data on the copied sheet.

The second-easiest way, if the chart's data is organized simply, is to use Select Data from the ribbon or the right-click menu, and change the range indicated in the Chart Data Range RefEdit at the top of the dialog.

A tedious way is to change all of the sheet references in all of the chart's series formulas, for example, changing all instances of Sheet1 in the following formula to Sheet2: =SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$4,Sheet1!$B$2:$B$4,1)

As @sancho.s points out, you can also do this with VBA using code posted in my Change Series Formula tutorial. These algorithms are built into my commercial Excel add-in software.

Answer 2

I've used Jon Peltier's Change Series Formula a lot (it is actually linked in the middle of the page provided in the answer by GGuess). It is an add-in that can be obtained here.

It is extremely useful, and it probably covers the needs of most cases of this type. It provides a convenient interface (UserForm) to search and replace strings in chart series formulas. It is actually more versatile than a "change source worksheets", since one can use search and replace for changing other parts of series formulas for many series at once.

Answer 3

So something like this worked for me. CopyCharts copies all charts from a source sheet to a target sheet. Then SetChartRef sets the reference of the charts in the target to what I want them to be. In this example I know which chart number is what. I guess it can be improved so that it uses the chart names instead.

Also, for some reason I get run time errors if I don't have delays between copying and pasting, hence the wait functions.

    Sub DeleteEmbeddedCharts(target As String)

    Dim wsItem As Worksheet
    Dim chtObj As ChartObject
        For Each chtObj In ThisWorkbook.Worksheets(target).ChartObjects
            chtObj.Delete
        Next
End Sub

Sub SetChartRef(target As String)

    Dim cht As ChartObject
    Dim i As Integer

    'i specifies which chart to set its data references
    i = 0
    For Each cht In ThisWorkbook.Worksheets(target).ChartObjects
        If i = 0 Then
            cht.Chart.SeriesCollection(1).Values = "=" & target & "!$I$2:$I$12"
            cht.Chart.SeriesCollection(2).Values = "=" & target & "!$J$2:$J$12"
        ElseIf i = 1 Then
             cht.Chart.SeriesCollection(1).Values = "=" & target & "!$I$14:$I$25"
             cht.Chart.SeriesCollection(2).Values = "=" & target & "!$J$14:$J$25"
        ElseIf i = 2 Then
            cht.Chart.SeriesCollection(1).Values = "=" & target & "!$I$26:$I$37"
            cht.Chart.SeriesCollection(2).Values = "=" & target & "!$J$26:$J$37"
        ElseIf i = 3 Then
            cht.Chart.SeriesCollection(1).Values = "=(" & target & "!$H$2," & target & "!$H$14," & target & "!$H$26," & target & "!$H$38)"
            cht.Chart.SeriesCollection(1).XValues = "=(" & target & "!$E$2," & target & "!$E$14," & target & "!$E$26," & target & "!$E$38)"
         ElseIf i = 4 Then
            cht.Chart.SeriesCollection(1).Values = "=(" & target & "!$H$2," & target & "!$H$14," & target & "!$H$26," & target & "!$H$38)"
            cht.Chart.SeriesCollection(1).XValues = "=(" & target & "!$E$2," & target & "!$E$14," & target & "!$E$26," & target & "!$E$38)"
        ElseIf i = 5 Then
            cht.Chart.SeriesCollection(1).Values = "=" & target & "!$I$38:$I$49"
            cht.Chart.SeriesCollection(2).Values = "=" & target & "!$J$38:$J$49"
        End If
        i = i + 1
    Next


End Sub

Sub CopyCharts(source As String, target As String)

    Dim chtObj As ChartObject
    'First delete all charts from target sheet
    DeleteEmbeddedCharts (target)

    'Some delay
    Application.Wait Now + TimeSerial(0, 0, 1)

    For Each chtObj In ThisWorkbook.Worksheets(source).ChartObjects
        With ThisWorkbook.Worksheets(target)
            .Activate
            chtObj.Copy
            'Paste in row T1+i
            Range("T1").Offset(i).Select
            .Activate
            Application.Wait Now + TimeSerial(0, 0, 1)
            .Paste
            Application.Wait Now + TimeSerial(0, 0, 1)
            i = i + 10
            .Activate
        End With
    Next chtObj

    'Set the data references to target sheet
    SetChartRef (target)

End Sub

Answer 4

Pelter has a good discussion on how to use a macro to edit the plot equations to reference the data in the current worksheet. See the article at http://peltiertech.com/WordPress/make-a-copied-chart-link-to-new-data/