Pivot Table Chart Number Changes Every Time I Run This Code

[Kurt]

Hello All,

I have the following code:

Sub Create_Pivot_Chart_2a()
'
' Create_Pivot_Chart_2a Macro
'

'
    Sheets("2").Select
    Range("Table9[#All]").Select
    Selection.Copy
    Sheets("5a").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Table978", Version:=6).CreatePivotTable TableDestination:="5a!R1C6", _
        TableName:="PivotTable5", DefaultVersion:=6
    Sheets("5a").Select
    Cells(3, 6).Select
    With ActiveSheet.PivotTables("PivotTable5")
        .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("PivotTable5").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("PivotTable5").RepeatAllLabels xlRepeatLabels
    ActiveWorkbook.ShowPivotTableFieldList = True
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("Name")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable5").AddDataField ActiveSheet.PivotTables( _
        "PivotTable5").PivotFields("Name"), "Count of Name", xlCount
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("DOB")
        .Orientation = xlRowField
        '.Position = 2
    End With
    ActiveSheet.PivotTables("PivotTable5").PivotFields("DOB").AutoGroup
    ActiveSheet.PivotTables("PivotTable5").PivotFields("Years").Orientation = _
        xlHidden
    ActiveSheet.PivotTables("PivotTable5").PivotFields("Quarters").Orientation = _
        xlHidden
    ActiveSheet.PivotTables("PivotTable5").AddDataField ActiveSheet.PivotTables( _
        "PivotTable5").PivotFields("DOB"), "Count of DOB", xlCount
    Range("F4").Select
    ActiveSheet.PivotTables("PivotTable5").PivotFields("Name").PivotFilters.Add2 _
        Type:=xlValueIsGreaterThanOrEqualTo, DataField:=ActiveSheet.PivotTables( _
        "PivotTable5").PivotFields("Count of Name"), Value1:=2
    Range("F5").Select
    ActiveSheet.PivotTables("PivotTable5").PivotFields("DOB").PivotFilters.Add2 _
        Type:=xlValueIsGreaterThanOrEqualTo, DataField:=ActiveSheet.PivotTables( _
        "PivotTable5").PivotFields("Count of Name"), Value1:=2
    ActiveSheet.Shapes.AddChart2(251, xlPie).Select
    ActiveChart.SetSourceData Source:=Range("'5a'!$F$3:$H$14")
    ActiveWorkbook.ShowPivotTableFieldList = False
    ActiveSheet.Shapes("Chart 10").IncrementLeft 381.75
    ActiveSheet.Shapes("Chart 10").IncrementTop -61.5
    ActiveChart.ChartTitle.Select
    Application.CommandBars("Format Object").Visible = False
    ActiveChart.ChartTitle.Text = "Names Occuring More Than Once In The Same Month"
    Selection.Format.TextFrame2.TextRange.Characters.Text = _
        "Names Occuring More Than Once In The Same Month"
    With Selection.Format.TextFrame2.TextRange.Characters(1, 47).ParagraphFormat
        .TextDirection = msoTextDirectionLeftToRight
        .Alignment = msoAlignCenter
    End With
    With Selection.Format.TextFrame2.TextRange.Characters(1, 5).Font
        .BaselineOffset = 0
        .Bold = msoFalse
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.RGB = RGB(89, 89, 89)
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 14
        .Italic = msoFalse
        .Kerning = 12
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
        .Spacing = 0
        .Strike = msoNoStrike
    End With
    With Selection.Format.TextFrame2.TextRange.Characters(6, 42).Font
        .BaselineOffset = 0
        .Bold = msoFalse
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.RGB = RGB(89, 89, 89)
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 14
        .Italic = msoFalse
        .Kerning = 12
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
        .Spacing = 0
        .Strike = msoNoStrike
    End With
    ActiveChart.FullSeriesCollection(1).Select
    ActiveChart.FullSeriesCollection(1).ApplyDataLabels
    ActiveChart.FullSeriesCollection(1).DataLabels.Select
    Selection.ShowPercentage = True
    Selection.ShowCategoryName = True
    Application.CommandBars("Format Object").Visible = False
    ActiveWindow.SmallScroll Down:=-15
    Range("E1").Select
End Sub

My code stops every time in this section.

How do I get the code to acount for a change in the name of the pivot chart and/or pivot table in this part of the code?

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Table978", Version:=6).CreatePivotTable TableDestination:="5a!R1C6", _
        TableName:="PivotTable5", DefaultVersion:=6

 

[mart37]

delete:
ActiveSheet.PivotTables("myPivotTable").AddDataField ActiveSheet.PivotTables( _
"myPivotTable").PivotFields("DOB"), "Count of DOB", xlCount

 

[mart37]

sorry wrong answer

 

[Kurt]

No problem thanks for your help so far.

I cannot understand why it is still sopping on the Months field.

 

[mart37]

Me too. I had to start Excel a few times again. Maybe you can do that also.
Do you delete the existing pivottable before you start the macro?
In your macro I can't see the macro for deleting pivottables.

 

[Kurt]

I will try and reboot.

I do not want to delete the pivot tables because I have to copy the pivot tables from Sheet 2.