Names changing in Pivot Table Macro

[ahdrum87]

Hello!


I am running into an error when creating a macro to make a pivot table and sum certain columns. I am fairly certain this is due to the name changing to PivotTable2, 3, 4, etc. each time. Is there a way to make the name stagnent so when a table is created in the macro it doesn't error out the formulas? I have an example below. Thanks so much in advance!

Columns("A:D").Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Data!R1C1:R1048576C4", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Pivot!R1C1", TableName:="PivotTable5", DefaultVersion _
        :=xlPivotTableVersion14
    Sheets("Pivot").Select
    Cells(1, 1).Select
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("PRIMARY NUMBER")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("OFF")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("PivotTable5").AddDataField ActiveSheet.PivotTables( _
        "PivotTable5").PivotFields("DEBIT"), "Count of DEBIT", xlCount
    ActiveSheet.PivotTables("PivotTable5").AddDataField ActiveSheet.PivotTables( _
        "PivotTable5").PivotFields("CREDIT"), "Count of CREDIT", xlCount
    Range("B1").Select
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("Count of DEBIT")
        .Caption = "Sum of DEBIT"
        .Function = xlSum
    End With
    Range("C1").Select
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("Count of CREDIT")
        .Caption = "Sum of CREDIT"
        .Function = xlSum
    End With
    Sheets("Summary").Select

 

[ahdrum87]

Does anyone have any information?

 

[mwrohr]

Are you pivoting the same data every time (understood that data is added/removed/changed each time poss.)? Why are you creating a new pivot table every time?