VBA Pivot table error

[k3vsmith]

Im creating VBA code to create a pivot table below. This code is giving me an error:
Run-time error '5':
Invalid procedure call or argument on the ActiveWorkbook line.
It creates the new worksheet and changes the name of the new worksheet but stops there. Here is the code. *Note - if I take replace Sheets.Add portion with only sheets.Add with no name and tabledestination with "Sheet#!R3C1" it works as it should. But Im trying to replace with variables to make it more friendly to use with other exports.
Any ideas?

Sub modpvtrecordingSIOP1111()

    Dim wsSIOP As Worksheet    
    Dim wsSIOPPVT As Worksheet
    Const cnwsSIOP = "SIOP_Report"
    Const cnwsSIOPPVT = "SIOP Pivot"

    Set wsSIOP = ActiveSheet
    wsSIOP.Name = cnwsSIOP
     
    Sheets.Add before:=wsSIOP
    Set wsSIOPPVT = ActiveSheet
    wsSIOPPVT.Name = cnwsSIOPPVT
   
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        wsSIOP.UsedRange, Version:=xlPivotTableVersion14). _
        CreatePivotTable TableDestination:=cnwsSIOPPVT, TableName:="SIOPPivot" _
        , DefaultVersion:=xlPivotTableVersion14
    Sheets(cnwsSIOPPVT).Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("SIOPPivot").PivotFields("YYYYMM")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("SIOPPivot").PivotFields("RES CODE")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("SIOPPivot").PivotFields("Business")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("SIOPPivot").CalculatedFields.Add "FTE", _
        "='HOURS/UNITS' /160", True
    ActiveSheet.PivotTables("SIOPPivot").PivotFields("FTE").Orientation = _
        xlDataField
    With ActiveSheet.PivotTables("SIOPPivot").PivotFields("Sum of FTE")
        .NumberFormat = "#0"
    End With
End Sub

 

[Chihiro]

Instead of using "ActiveSheet" use following.

Set wsSIOPPVT = Sheets.Add before:=wsSIOP

And modify rest of your code where ActiveSheet is used with wsSIOPPVT.

 

[Debaser]

It's also safer to use an address string in R1C1 format rather than a range object when creating the cache, and I like to have that as a separate step to creating the pivot table since it makes debugging simpler. For example:

Sub modpvtrecordingSIOP1111()

  Dim wsSIOP  As Excel.Worksheet
  Dim wsSIOPPVT  As Excel.Worksheet
  Dim PC  As Excel.PivotCache
  Dim PT  As Excel.PivotTable
  Const cnwsSIOP = "SIOP_Report"
  Const cnwsSIOPPVT = "SIOP Pivot"

  Set wsSIOP = ActiveSheet
  wsSIOP.Name = cnwsSIOP

  Set wsSIOPPVT = Sheets.Add(before:=wsSIOP)
  wsSIOPPVT.Name = cnwsSIOPPVT

  Set PC = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
  SourceData:="'" & wsSIOP.Name & "'!" & wsSIOP.UsedRange.Address(ReferenceStyle:=xlR1C1), _
  Version:=xlPivotTableVersion14)
  Set PT = PC.CreatePivotTable(TableDestination:=wsSIOPPVT.Cells(3, 1), _
  TableName:="SIOPPivot", _
  DefaultVersion:=xlPivotTableVersion14)

  With PT
  With .PivotFields("YYYYMM")
  .Orientation = xlRowField
  .Position = 1
  End With
  With .PivotFields("RES CODE")
  .Orientation = xlPageField
  .Position = 1
  End With
  With .PivotFields("Business")
  .Orientation = xlColumnField
  .Position = 1
  End With
  .CalculatedFields.Add "FTE", "='HOURS/UNITS' /160", True
  With .PivotFields("FTE")
  .Orientation = xlDataField
  .NumberFormat = "#0"
  End With
  End With
End Sub

 

[k3vsmith]

Awesome! Thanks you guys both. This works nicely