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 · Accepted Answer · 2014-06-10 17:58:51Z

3

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

answered Jun 10, 2014 at 17:58

ARich

3,2995 gold badges33 silver badges56 bronze badges

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

Collectives™ on Stack Overflow

Excel VBA delete pivot chart with macro

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related