Tom’s Tutorials For Excel: Move Embedded Charts to Their Own Chart Sheet

Tom’s Tutorials For Excel: Move Embedded Charts to Their Own Chart Sheet

Here is a macro that will move all embedded charts on a worksheet to their own Chart sheet.

The Select method for cell A1 (it could be any cell but I chose A1) is to avoid a run time error which could occur if an embedded chart is active when a new sheet is attempted to be added.

Sub MoveCharts()
Application.ScreenUpdating = False
Dim i%, asn$
With ActiveSheet
asn = .Name
For i = Sheets(asn).ChartObjects.Count To 1 Step -1
Sheets(asn).Activate
Range("A1").Select
.ChartObjects(i).Activate
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Chart" & i
Next i
End With
Application.ScreenUpdating = True
End Sub