(Solved) – Pivot Chart Macro Recording – Chart Name Causing Run-Tim Error


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.

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

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "'Tags Data Dump'!R1C1:R200C31", Version:=6).CreatePivotTable _
        TableDestination:="'NITS Pivot Chart'!R1C1", TableName:="PivotTable15", _
    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

Leave a Reply

Your email address will not be published. Required fields are marked *