This assume the following.
First worksheet contains chart to be copied.
All other worksheets have exactly the same data layout
Sub CopyChartObject()
Dim shtData As Worksheet
Dim chtTemp As ChartObject
Dim strOldName As String
Dim strNewName As String
Dim lngIndex As Long
Dim lngSeries As Long
Set chtTemp = ThisWorkbook.Worksheets(1).ChartObjects(1)
strOldName = chtTemp.Parent.Name
For lngIndex = 2 To ThisWorkbook.Worksheets.Count
chtTemp.Copy
With ThisWorkbook.Worksheets(lngIndex)
.Paste
strNewName = .Name
With .ChartObjects(.ChartObjects.Count).Chart
For lngSeries = 1 To .SeriesCollection.Count
.SeriesCollection(lngSeries).Formula = Replace(.SeriesCollection(lngSeries).Formula, strOldName, strNewName)
Next
End With
End With
Next
End Sub
Bookmarks