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.
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
Since Excel 2007, chart sheets can no longer size with the window. This means there’s really no longer any benefit to using chart sheets.