cellMatrix.net

VBA to Copy Embedded Chart Formatting

A recent post to the Microsoft Excel Charting Discussion group asked "how do I copy the formatting for one embedded chart to another 230 charts in a workbook?" I responded with the following . . . select the embedded chart that you consider your formatted source or template. Next, run the following macro:

Sub Copy_Chart_Formats()

    Dim Sht As Worksheet
    Dim Cht As ChartObject

    Application.ScreenUpdating = False

    ActiveChart.ChartArea.Copy

    For Each Sht In ActiveWorkbook.Sheets
        Sht.Activate
        For Each Cht In ActiveSheet.ChartObjects
            Cht.Activate
            ActiveChart.Paste Type:=xlFormats
        Next Cht
    Next Sht

End Sub

Although the macro works I don't think it's written well because it individually activates each sheet and embedded chart to copy the formats. How could this macro be improved?

UPDATE

Upon testing this procedure, Jon Peltier noted that it failed to copy title formatting. Jon's recent post titled Apply Chart Formatting to Other Charts rewrites the procedure to copy all chart formatting.