Move existing chart on Excel with VBA

Question

I have two worksheets: PivotTables and Charts. I created,formatted and moved a chart from the PivotTables sheet to the Charts sheet. Now I just want to move it from the default cell to the cells I want but I keep failing in doing so.

When I run the code below I get a run-time error: "Method 'Left' of object 'ChartObject' failed". What am I doing wrong?

Sub CreateChart()

    Sheets("PivotTables").Activate

    Dim myChart2 As chart
    Set myChart2 = Sheets("PivotTables").Shapes.AddChart.chart

    With myChart2
        .SetSourceData Source:=Range("D1").CurrentRegion
        .ChartType = xlColumnClustered
        .HasTitle = True
        .ChartTitle.Text = "Sum of Impressions by Product"
        .SeriesCollection(1).Format.Fill.ForeColor.RGB = rgbBlue
        .ShowAllFieldButtons = False
        .Location xlLocationAsObject, "Charts"
    End With

    Sheets("Charts").Activate

    With myChart2.Parent
        .Left = Sheets("Charts").Range("F1").Left
        .Top = Sheets("Charts").Range("F1").Top
        .Width = Sheets("Charts").Range("F1:J1").Width
        .Height = Sheets("Charts").Range("F1:F10").Height
    End With

End Sub

Christine Ross Christine Ross · Accepted Answer · 2015-04-28T11:52:57

Your code performs somethings different from your text description.

You create a chart "Chart2" on the worksheet "Pivottables"
you should copy or cut the chart from this worksheet
Then you can use "paste" or "pastespecial" to insert the chart in worksheet "charts"
Now you can format and/or shift position on this worksheet

When you change left, top etc. it doesn't move the chart from sheet to sheet

Move existing chart on Excel with VBA

2 Answers