Hi,
New to the forums here. I've been working on a macro to create multiple pivot tables in new, different worksheets. I have this broken down into 2 steps or 2 separate Macros (Step 1 creates 1 pivot table in a new sheet and step 2 creates a separate pivot table in another new sheet). I cant seem to figure out why Step 1 works fine and creates the pivot table to add to a new sheet, and then step 2 does not work. I've provided VBAs for the below.
Also, I'm running Step 1 macro and then running Step 2, not sure if this quite makes a difference.
Appreciate your assistance! Thank you!
Step 1
Step_2 (Doesnt work - syntax error somewhere in red below)
New to the forums here. I've been working on a macro to create multiple pivot tables in new, different worksheets. I have this broken down into 2 steps or 2 separate Macros (Step 1 creates 1 pivot table in a new sheet and step 2 creates a separate pivot table in another new sheet). I cant seem to figure out why Step 1 works fine and creates the pivot table to add to a new sheet, and then step 2 does not work. I've provided VBAs for the below.
Also, I'm running Step 1 macro and then running Step 2, not sure if this quite makes a difference.
Appreciate your assistance! Thank you!
Step 1
Rich (BB code):
Dim dataname As String
Dim newsheet As String
dataname = ActiveSheet.ListObjects(1).Name
Application.CutCopyMode = False
Sheets.Add
newsheet = ActiveSheet.Name
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
dataname, Version:=7).CreatePivotTable TableDestination:=newsheet & "!R3C1", _
TableName:="PivotTable2", DefaultVersion:=7
Sheets(newsheet).Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable2")
Step_2 (Doesnt work - syntax error somewhere in red below)
Rich (BB code):
Dim dataname As String
Dim newsheet As String
dataname = ActiveSheet.ListObjects(1).Name
Application.CutCopyMode = False
Sheets.Add
newsheet = ActiveSheet.Name
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
dataname, Version:=7).CreatePivotTable TableDestination:=newsheet & "!R3C1",
TableName:="PivotTable4", DefaultVersion:=7
Sheets(newsheet).Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable4")
Last edited by a moderator: