Excel – Copying Chart to Another Sheet with Updated References in Excel

chartsmicrosoft excelmicrosoft-excel-2013microsoft-officeworksheet-function

I have made a chart within a sheet in Excel. Within the sheet, the chart refers to specific cells in the sheet to display (i.e. cells that contain my data points).

I would like to know how to copy the chart into another sheet within my Excel file, and have the references update to refer to the cell addresses that are within the its new sheet.

Whenever you copy-paste/move a chart into another sheet, the chart will still refer to cells within the original sheet it was made in. This is because anytime you refer to a cell within a chart, it specifically refers to the sheet it was made it (i.e. Instead of saying $T$7:$T$12, references are made to be SHEET'!$T$7:$T$12).

In the references within the chart, if I change the reference from SHEET'!$T$7:$T$12 to $T$7:$T$12 (or even T7:T12), I get a message saying that my formula contains an error.

How can I solve this issue?

Thanks.

Best Answer

The easiest way to handle this is to copy the entire worksheet with data and chart, then paste the new data into the copied worksheet. It's a simple enough protocol, if time-consuming, but there's more description in my tutorial at Copy Chart to New Sheet and Link to Data on New Sheet.

An alternative is to copy the chart to the new sheet use a VBA procedure to modify the series formulas in the copied chart, to replace the old sheet's name with the new sheet's name. The VBA is basically this:

Sub FindReplaceSeriesFormula(myChart As Chart, OldText As String, NewText As String)
  Dim srs As Series

  For Each srs In myChart.SeriesCollection
    srs.Formula = Replace(srs.Formula, OldText, NewText)
  Next
End Sub

and you would call it like this:

Sub FixActiveChart()
  FindReplaceSeriesFormula ActiveChart, "Sheet1", "Sheet2"
End Sub

I discuss this approach and provide some more bulletproof code in my tutorial Change Series Formula – Improved Routines

Related Question