title: Excel VBA delete pivot chart with macro
body: <p>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.</p>
  
  <p>Here's my table/chart creation:</p>
  
  <pre><code>   Sheets("EditListingCriteria").Select
      RowNumber = "R" &amp; Application.CountA(Range("A:A")) &amp; "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
  </code></pre>
  
  <p>And the Delete:</p>
  
  <pre><code>ActiveSheet.ChartObjects("Chart 10").Activate  
  ActiveChart.Parent.delete
  </code></pre>
  
  <p>I tried the follow to rename it but it always breaks</p>
  
  <pre><code> chartname = ActiveChart.Name
  ActiveSheet.Shapes(chartname).Name = "MonthlyChart"
  </code></pre>