Excel VBA delete pivot chart with macro

Question

I have a button on my excel sheet that creates a pivot table and pivot chart when clicked. As it is going to be run many times, each time it runs I need it delete the existing table and chart. The table is no problem, but the chart gets renamed with an incremental number each time. I need to know the name of chart on run to be able to delete it.

Here's my table/chart creation:

   Sheets("EditListingCriteria").Select
    RowNumber = "R" & Application.CountA(Range("A:A")) & "C19"

    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("PivotTable").PivotTables("PivotTable5").PivotCache. _
        CreatePivotTable TableDestination:="Charts!R5C3", TableName:="PivotTable6" _
        , DefaultVersion:=xlPivotTableVersion14
    Sheets("Charts").Select
    Cells(5, 3).Select
    ActiveSheet.PivotTables("PivotTable6").AddDataField ActiveSheet.PivotTables( _
        "PivotTable6").PivotFields("Material Weight"), "Sum of Material Weight", xlSum
    With ActiveSheet.PivotTables("PivotTable6").PivotFields("Shipment Month")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable6").PivotFields("Ordered Ship Mode")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable6").PivotFields("Actual Ship Mode")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xl3DColumnClustered
    ActiveChart.SetSourceData Source:=Range("Charts!$C$5:$F$12")
    'Rename chart here?
    Rows("2:12").RowHeight = 0
    Rows("1:1").RowHeight = 0

And the Delete:

ActiveSheet.ChartObjects("Chart 10").Activate  
ActiveChart.Parent.delete

I tried the follow to rename it but it always breaks

 chartname = ActiveChart.Name
ActiveSheet.Shapes(chartname).Name = "MonthlyChart"

ARich ARich · Accepted Answer · 2014-06-10T17:58:51

Try this:

'To create a chart
Dim S As Worksheet
Dim C As Chart

Set S = Activesheet
Set C = S.Shapes.AddChart.Chart
S.Shapes(C.Parent.Name).Name = "MonthlyChart"

'To delete the chart later in the same sub:
C.Parent.Delete

'Or if your delete code is in a different sub (I suspect it is):
Dim S As Worksheet
Set S = Activesheet

S.ChartObjects("MonthlyChart").Delete

Excel VBA delete pivot chart with macro

1 Answers