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

Zerk · Accepted Answer · 2015-04-28 12:06:19Z

1

I can't quite answer the why, but there seemed to be a conflict in the ordering between .Location xlLocationAsObject, "Charts" and the position setting with .left/etc

Moving the chart on the sheet prior to moving it between sheets with .location seemed to fix your problem.

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
        With .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
        .Location xlLocationAsObject, "Charts"
    End With

    Sheets("Charts").Activate



End Sub

answered Apr 28, 2015 at 12:06

Zerk

1,5939 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Victor Over a year ago

Thanks!! I was wondering if this would be the best approach though. Would you do a PivotChart instead?

Zerk Over a year ago

It's hard to say without knowing the exact situation. I'd most likely have a chart in place already. I would then possibly have a macro which could potentially be triggered by the worksheet change event to re-populate itself via .setsourcedata to dynamically update the data being displayed.

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

1

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

answered Apr 28, 2015 at 11:52

Christine Ross

4635 silver badges15 bronze badges

Collectives™ on Stack Overflow

Move existing chart on Excel with VBA

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related