Runtime Error 5 VBA Creating PivotTable - Default Version-CodePudding

I am trying to create a Pivot Table using VBA, however I am encountering the following error:

"Run-time error '5':

Invalid procedure call or argument"

Now, I used this question/answers to try and fix my issue Runtime Error 5 Sometimes in VBA Creating PivotTable

I recorded my macro to see what code it would produce if I made a Pivot Table normally. I would create the Pivot Table with an existing worksheet called "Report". Here is the result: (I have removed most of the code in this snippet as a lot is PivotTable properties True/False etc)

    Range("A3:CX1048576").Select
    Range("F3816").Activate
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "OrderManagementData!R3C1:R1048576C102", Version:=8).CreatePivotTable _
        TableDestination:="Stock & WIP Report!R1:R1048576", TableName:= _
        "PivotTable2", DefaultVersion:=8
    Sheets("Report").Select
    Cells(1, 1).Select
    With ActiveSheet.PivotTables("PivotTable1")
        .ColumnGrand = True
        .RowAxisLayout xlCompactRow
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("PivotTable1").RepeatAllLabels xlRepeatLabels

Now, when I debug the error, it highlights this section of the code:

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "OrderManagementData!R3C1:R1048576C102", Version:=8).CreatePivotTable _
        TableDestination:="Stock & WIP Report!R1:R1048576", TableName:= _
        "PivotTable2", DefaultVersion:=8

I tried changed the version/default version to any of the following: xlPivotTableVersion15, xlPivotTableVersion14, xlPivotTableVersion12 but nothing worked.

My version of Excel isnt simply 2010 or 2013. When I click File -> Account -> About Excel, it says the following information: Microsoft® Excel® for Microsoft 365 MSO (Version 2210 Build 16.0.15726.20070) 64-bit

Any idea how I can create a Pivot Table? Spent a while on this and I am stumped

CodePudding user response：

Try the following:

TableDestination:="Stock & WIP Report!R1C1"

TableDestination:=ThisWorkbook.Worksheets("Stock & WIP Report").Range("A1")

Note that Version:=8 and DefaultVersion:=8 are both optional arguments, as described in the PivotCaches.Create and PivotCache.CreatePivotTable documentation.