Pivot Chart Macro Recording - Chart Name Causing Run-Time Error

Question

Recording a macro of pivot chart creation. Creating a pivot table and chart, and then editing the chart's properties.

The code runs fine until it tries to to edit the chart's properties. At which point I get this message:

"Run-Time error '2147024809 (80070057)'

The item with the specified name wasn't found

It seems that the chart naming is the issue. Example: when I record the macro, Excel names the chart "Chart 1". But when I recreate the scenario, the chart is then called "Chart 2" and so the macro doesn't work. If I go and manually change the name to "Chart 2", the code finishes executing fine.

If I try to name the chart while recording the macro, I run into a similar issue. i.e., it tries to change "Chart 1" to "New Chart Name", but can't find "Chart 1".

Not too experienced with VBA, but I assume there is some way to name the chart. I tried adding a simple...

ActiveChart.Name = "Name of this Chart"

...following the creation of the chart, but that didn't work.

Full code below. Any help is greatly appreicated!

'
' Create_NITS_Pivot Macro
'

'
    Columns("A:AE").Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "'Tags Data Dump'!R1C1:R200C31", Version:=6).CreatePivotTable _
        TableDestination:="'NITS Pivot Chart'!R1C1", TableName:="PivotTable15", _
        DefaultVersion:=6
    Sheets("NITS Pivot Chart").Select
    Cells(1, 1).Select
    With ActiveSheet.PivotTables("PivotTable15")
        .ColumnGrand = True
        .HasAutoFormat = True
        .DisplayErrorString = False
        .DisplayNullString = True
        .EnableDrilldown = True
        .ErrorString = ""
        .MergeLabels = False
        .NullString = ""
        .PageFieldOrder = 2
        .PageFieldWrapCount = 0
        .PreserveFormatting = True
        .RowGrand = True
        .SaveData = True
        .PrintTitles = False
        .RepeatItemsOnEachPrintedPage = True
        .TotalsAnnotation = False
        .CompactRowIndent = 1
        .InGridDropZones = False
        .DisplayFieldCaptions = True
        .DisplayMemberPropertyTooltips = False
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .PrintDrillIndicators = False
        .AllowMultipleFilters = False
        .SortUsingCustomLists = True
        .FieldListSortAscending = False
        .ShowValuesRow = False
        .CalculatedMembersInFilters = False
        .RowAxisLayout xlCompactRow
    End With
    With ActiveSheet.PivotTables("PivotTable15").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("PivotTable15").RepeatAllLabels xlRepeatLabels
    ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
    ActiveChart.SetSourceData Source:=Range("'NITS Pivot Chart'!$A$1:$C$18")
    ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout. _
        PivotTable.PivotFields("NITS 16"), "Sum of NITS 16", xlSum
    ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout. _
        PivotTable.PivotFields("NITS 17"), "Sum of NITS 17", xlSum
    ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout. _
        PivotTable.PivotFields("NITS 18"), "Sum of NITS 18", xlSum
    ActiveChart.Name = "NITS_Chart"
    With ActiveChart.PivotLayout.PivotTable.DataPivotField
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveChart.PivotLayout.PivotTable.PivotFields("Utility Acct/Cust#")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveChart.PivotLayout.PivotTable.PivotFields("Sub-account name")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.Shapes("Chart 2").IncrementLeft -1030.5
    ActiveSheet.Shapes("Chart 2").IncrementTop -192.5
End Sub

Tim Williams Tim Williams · Accepted Answer · 2020-03-26T21:45:24

The best approach here is to get into the habit of capturing the return value from methods like AddChart:

Dim co As Shape
Set co = ActiveSheet.Shapes.AddChart()

Here co is a Shape/ChartObject which you can reference later in your code, instead of tagging everything with .Select and using ActiveThing to hope to reach it later.

So:

co.IncrementLeft -1030.5

or to reference the contained Chart:

co.Chart.SetSourceData

etc etc.

Same goes for creating pivotcache and pivotable objects.

Pivot Chart Macro Recording - Chart Name Causing Run-Time Error

1 Answers