title: Create a pivot table in a predefined location
body: <p>I'm trying to create a pivot table and place it in a predefined location (not a new sheet).</p>
  
  <p>Before running the macro each time, the pivot table is deleted and also the predefined sheet.</p>
  
  <p>I noticed that when you create a table manually, the table name increases by one each time (PivotTable2, PivotTable3...), which I think is where my code is falling down.</p>
  
  <p>I get a Run-time error 5, invalid procedure call or argument on this line:</p>
  
  <pre><code>ActiveWorkbook.PivotCaches.Create
  </code></pre>
  
  <p>I did check out <a href="https://stackoverflow.com/questions/30509352/runtime-error-1004-while-creating-a-pivot-table">this</a> thread, which says that you can remove the table name parameter completely, or rename it - however I still get errors. </p>
  
  <p>My code:</p>
  
  <pre><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
  </code></pre>