Pivot Table increasing by 1 each time when creating a new table

[APage]

Hi there,

Sorry for the naff title - this is my first post and still a rookie in the VBA world!

So I'm trying to write code which creates a pivot table and places it in a predefined location (not a new sheet). Before running the macro each time, the pivot table is deleted.

The issue:

This works fantastic for the first run - but doesn't work for the following attempts.

My suspicion is each time a pivot is created - it's name increases by one each time... 'PivotTable1, PivotTable2...'

My code:

Sub CreatePivot()'
' CreatePivot Macro
'


' Set data as table
    Sheets("Filtered Flags").Select
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$G$16000"), , xlYes).Name _
        = "Table1"
        
' Create worksheet for pivot output
    Sheets.Add Before:=ActiveSheet
    ActiveSheet.Name = "Flag Pivot"
    
'Create Pivot Table
      Sheets("Filtered Flags").Select
    Range("Table1[[#Headers],[Order '#]]").Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Table1", Version:=6).CreatePivotTable TableDestination:="Flag Pivot!R3C1" _
        , TableName:="PivotTable5", DefaultVersion:=6
    Sheets("Flag Pivot").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("Material #")
        .Orientation = xlRowField
        .Position = 1
    End With


End Sub

Sorry if this makes little to zero sense! Bare with me while I try to crack the lingo

- Anna