Procedure Error when creating a pivot table using VBA

Question

When I run the below VBA code to create a pivot table, I get an "Invalid procedure or call argument error" when running the macro. The error is on the "Set PT" area.

I've defined worksheet objects but I also tried adjusting the "Table Destination" to directly reference the worksheet and I still get the same error.

Any idea what might be causing the error?

With wsData
    Dim PTCache As PivotCache
    Dim PT As PivotTable

    'Creates the Cache
    Set PTCache = ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=.Range("A1").CurrentRegion)

    'Creates pivot table
    Set PT = wsPivot.PivotTables.Add( _
        PivotCache:=PTCache, _
        TableDestination:=wsPivot)

    'Defines fields
    With PT
        .PivotFields("Field 1").Orientation = xlRowField
        .PivotFields("Field 2").Orientation = xlRowField
        .PivotFields("Field 3").Orientation = xlRowField
        .AddDataField .PivotFields("Field 4"), "Field 4", xlCount
        .TableStyle2 = "PivotStyleMedium2"
    End With
End With

Its a worksheet object, equal to something like '[Worksheet.xlsm]Sheets("Pivot")' — Cody Alan
– Cody Alan, Commented Feb 7, 2018 at 21:08
TableDestination argument requires a specified range. See PivotTables.Add Method — Scott Holtzman
– Scott Holtzman, Commented Feb 7, 2018 at 21:11
Thanks. Based off that information I tweaked that section to be: Set PT = wsPivot.PivotTables.Add( _ PivotCache:=PTCache, _ TableDestination:=wsPivot.Range("A3"), _ TableName:="Pivot Table") I still got the same error : "Run-time error '5': Invalid procedure call or argument" — Cody Alan
– Cody Alan, Commented Feb 7, 2018 at 21:32

Cody Alan · Accepted Answer · 2018-02-07 23:00:06Z

1

I found the issue, rather than building the pivot cache from the ActiveWorkbook I used ThisWorkbook instead.

The following code ran without error:

With wsData
Dim PTCache As PivotCache
Dim PT As PivotTable

'Creates the Cache
Set PTCache = ThisWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=.Range("A1").CurrentRegion)

'Creates pivot table
Set PT = wsPivot.PivotTables.Add( _
    PivotCache:=PTCache, _
    TableDestination:=wsPivot)

'Defines fields
With PT
    .PivotFields("Field 1").Orientation = xlRowField
    .PivotFields("Field 2").Orientation = xlRowField
    .PivotFields("Field 3").Orientation = xlRowField
    .AddDataField .PivotFields("Field 4"), "Field 4", xlCount
    .TableStyle2 = "PivotStyleMedium2"
End With
End With

answered Feb 7, 2018 at 23:00

Cody Alan

231 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Procedure Error when creating a pivot table using VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related