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

Question

I have several sheets that have different data but are organized in the same way (same number of columns and rows). I created some diagrams in sheet1 that I would like to copy to sheet2. while plain copy / paste copies the diagrams to sheet2, the diagrams still refer to the data in sheet1 rather than sheet2. How can I automatically force them to use sheet2 data instead of sheet after copying?

As a job, I tried copying sheet1 and named it sheet2 (which copies all the data and charts) and then copies and pastes the real data sheet2 into this new sheet. This works, but I was hoping there was a faster way and possibly a macro that copies all the diagrams from sheet1 to sheet2 and automatically updates the links.

Answer 1

So something like this worked for me. CopyCharts copies all charts from the source sheet to the target sheet. Then SetChartRef establishes the binding of the diagrams in the target to what I want them to be. In this example, I know what chart number is. I think this can be improved to use chart names instead.

Also, for some reason, I get runtime errors if I have no delays between copy and paste, hence the wait function.

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 2

The easiest way to copy a chart to another sheet and snap the chart to the data in the new sheet is not to copy the chart. The easiest way is to copy the sheet, including the chart, and then change the data on the copied sheet.

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

A very tedious way is to change all sheet references in all formulas of a series of diagrams, for example, change 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 the code posted in my Change Series Formula . These algorithms are built into my commercial Excel add-in software.

Answer 3

I used Jon Peltier Change Formula series (it is actually linked in the middle of the page provided in the GGuess answer). This is the add-on that you can get here .

This is extremely useful, and it probably covers the needs of most cases of this type. It provides a user-friendly interface (UserForm) for finding and replacing strings in formulas of a series of diagrams. This is actually more universal than “change worksheets,” since you can use search and replace to change other parts of serial formulas for many series at once.

Answer 4

Pelter discusses well how to use a macro to edit plot equations to reference data on the current sheet. See the article at http://peltiertech.com/WordPress/make-a-copied-chart-link-to-new-data/