cellMatrix.net

Copy Embedded Charts as Shapes with VBA

Andy Pope recently posted a nice bit of code in response to a question posted in the Microsoft Excel Charting Discussion Group. The question asked how to copy a chart as a shape without using a selection event. I've struggled with this same question. Specifically, how do you to copy charts as shapes across different sheets without selecting the destination sheet. Andy's example does the trick:

Sub CopyChartsAsShapes()

    Application.ScreenUpdating = False

    Dim Cht As Chart
    Dim Rng As Range

    Set Cht = Sheets("MC").ChartObjects("Cht1").Chart

    For Each Rng In Sheets("Dash").Range("F4:F11")

        Cht.CopyPicture
        With Rng.Parent
            .Paste
            With .Shapes(.Shapes.Count)
                .LockAspectRatio = msoFalse
                .Left = Rng.Left
                .Top = Rng.Top
                .Width = Rng.Width
                .Height = Rng.Height
            End With
        End With

    Next Rng

    Cht.Deselect
    
    Application.ScreenUpdating = True

End Sub

In the example, the procedure copies a chart that resides on the sheet called "MC" to each cell within the range F4:F11 on the sheet called "Dash". The poster had named the chart "Cht1", which can easily be done by activating, or clicking on the embedded chart and then running the macro below:

Sub NameChart()
    ActiveChart.Parent.Name = "Cht1"
End Sub