VBA - Creating Pivot Tables in New Sheets using Dynamic Objects ?

[Lambrix]

Greetings,

I am trying to solve in VBA how to create pivot tables in new sheets using objects instead of relying on the sheetcounter, which errors out.

I found an old Tip on Mr. Excel: Excel Create and name a new worksheet with VBA

But I can't figure out how to apply the object to the rest of the script. Here is what I have so far:


Sub Macro2()
'
' Macro2 Macro
'
Dim WS As Worksheet
Set WS = Sheets.Add
'
Range("A5:P2683").Select
WS
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"September Raw!R5C1:R2683C16", Version:=xlPivotTableVersion14). _
CreatePivotTable TableDestination:="Sheet2!R3C1", TableName:="PivotTable1" _
, DefaultVersion:=xlPivotTableVersion14
Sheets("Sheet2").Select
Cells(3, 1).Select
End Sub


Thank you.

David

 

[RoryA]

Not really clear what you want, but maybe:

Sub Macro2()
'
' Macro2 Macro
'
Dim WS As Worksheet
Set WS = Sheets.Add
'
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"'September Raw'!R5C1:R2683C16", Version:=xlPivotTableVersion14). _
CreatePivotTable TableDestination:=ws.range("A3"), TableName:="PivotTable1" _
, DefaultVersion:=xlPivotTableVersion14

End Sub