VBA Create New Pivot Table

Question

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.

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

to change a sheet name you can use Sheets("The name of the shee").Name = "The name you want" — Damian
Hi Damian, that doesn't work... We've just created a new sheet with Sheets.Add, let's call it Sheet15. The code will have to be Sheets("Sheet15").Name = "Pivot" but I don't want to keep opening up the macro and editing "Sheet15," if you follow... — Marshall Gu
So you will run this macro only once everytime you need it. And you need to store the previous outputs? — Damian
Why not set ws = application.thisworkbook.sheets.add and then ws will be dynamic and ws.name will work like damian suggested. You can then just have an integer counter if you need the name to be unique and you intend to store all previous outputs. — SadMrFrown
Mostly because I wasn't aware that was a solution (not even 'decent' at VBA...still learning). Trying to use ws = application.thisworkboox.sheets.add and I'm getting Runt-ime error '438': Object doesn't support this property or method. — Marshall Gu

Unknown Unknown · Accepted Answer · 2019-03-05T16:20:33

To sum it up:

Option Explicit

Sub test()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets.Add
    ws.Name = "PivotSheet"
End Sub

VBA Create New Pivot Table

1 Answers