title: VBA Create New Pivot Table
body: <p>I recorded a macro to create a pivot table based on data in the sheet "Data." The problem is I'm not sure how to create a new sheet for the pivot table (Sheets.Add) and then immediately give it a name, since if I run this over and over I have to keep changing "SheetX" to "SheetX+1" and so on.</p>
  
  <pre><code>Sheets("Data").Select
  Sheets.Add
  ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
      "Feb Data!R1C1:R3000C150", Version:=6).CreatePivotTable TableDestination:= _
      "Sheet9!R3C1", TableName:="PivotTable1", DefaultVersion:=6
  Sheets("Sheet9").Select
  </code></pre>