Macro - Create Pivot Table

[Mundy727]

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

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)

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")

 

[Colo]

Hi, it seems to lack "_" on the second line in red for making VBA understand it's one code line.

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
                                      dataname, Version:=7).CreatePivotTable TableDestination:=newsheet & "!R3C1", _
    TableName:="PivotTable4", DefaultVersion:=7

 

[Mundy727]

Thank you Colo! I updated via the below screenshot and still shows Syntax error. Any thoughts?

Appreciate your help!

 

[RoryA]

You need a space after the comma and before the underscore.

 

[Mundy727]

Awesome that worked, thanks a bunch!