Pivot Table VBA driving me insane!

[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

 

[Fluff]

Duplicate https://www.mrexcel.com/forum/excel...time-when-creating-new-table.html#post5057401

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

Note that sometimes posts from new users require Moderator approval before you can see them on the public forums. When this happens, you should see a message to that effect when you try to post it.
Please be patient and do not attempt to post the question again.