vba PivotTable - Application Defined or object Defined Error

Question

I'm trying to create a PivotTable using excel VBA. I've learned that this can be tricky and that it is advisable to Dim the PivotRange as String, using R1C1-reference style. I've copy-pasted code I wrote that worked into a new macro and I'm getting an application Defined or object defined error on the row indicated below. The Pivot Table however appears already on the worksheet, but the fields are obviously not generated, as the macro refuses to continue.

Question: does anyone know what is causing the error?

Code:

    Option Explicit

Public wbTO As Workbook
Public ws As Worksheet
Public LastRow As Long, LastCol As Long

Sub CreatePivot()

Dim wbEFiche As Workbook
    Dim wsEF As Worksheet

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
Application.AskToUpdateLinks = False

Set wbTO = ThisWorkbook
Set ws = wbTO.Sheets(1)
Set wsEF = wbTO.Worksheets("Result")

LastCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column
LastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
Set Rng = ws.Range(ws.Cells(1, 1), ws.Cells(LastRow, LastCol))

'Create Pivot table
    Dim PivotRange As String
    Dim PCache As PivotCache
    Dim PTable As PivotTable
    'Due to an excel bug, the range has to be encoded in a string format, using a R1C1 reference style
    PivotRange = ws.Name & "!" & ws.Range(ws.Cells(1, 1), ws.Cells(LastRow, LastCol)).Address(ReferenceStyle:=xlA1)
    'Set the pivot cache
    Debug.Print PivotRange
    'Debug.Print result = Sheet1!$A$1:$BH$168
    'ERROR ON THE FOLLOWING LINE
    Set PCache = wbTO.PivotCaches.Create _
    (SourceType:=xlDatabase, SourceData:=PivotRange). _
    CreatePivotTable(TableDestination:=wsEF.Cells(12, 7), _
    TableName:="PostOccupationTable")
    'Create a blank pivot table
    Set PTable = PCache.CreatePivotTable _
    (TableDestination:=wsEF.Cells(12, 7), TableName:="PostOccupationTable")
    'Insert Row & Column Fields
     With wsEF.PivotTables("PostOccupationTable").PivotFields("IndCategory2")
        .Orientation = xlRowField
        .Position = 1
    End With
    With wsEF.PivotTables("PostOccupationTable").PivotFields("PostCategory2")
        .Orientation = xlColumnField
        .Position = 1
    End With
    'Insert data field
    wsEF.PivotTables("PostOccupationTable").AddDataField ActiveSheet.PivotTables( _
        "PostOccupationTable").PivotFields("StamNr"), "Count of StamNr", xlCount
    'Set filters
    With wsEF.PivotTables("PostOccupationTable").PivotFields("PostCategory2")
        .PivotItems("(blank)").Visible = False
    End With
    With wsEF.PivotTables("PostOccupationTable").PivotFields("IndCategory2")
        .PivotItems("(blank)").Visible = False
    End With
    'Apply Style
    wsEF.PivotTables("PostOccupationTable").TableStyle2 = _
        "PivotStyleMedium2"

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.AskToUpdateLinks = True



End Sub

just below the comment "ERROR ON THE FOLLOWING LINE", being the Set PCache — Lambik

Lambik Lambik · Accepted Answer · 2019-02-11T10:29:01

Change

Set PCache = wbTO.PivotCaches.Create _
    (SourceType:=xlDatabase, SourceData:=PivotRange). _
    CreatePivotTable(TableDestination:=wsEF.Cells(12, 7), _
    TableName:="PostOccupationTable")

Into

Set PCache = wbTO.PivotCaches.Create _
    (SourceType:=xlDatabase, SourceData:=PivotRange)

The create Pivot table is done on the next line, so wasn't really required. Apparently caused an error, I don't really know why...

vba PivotTable - Application Defined or object Defined Error

1 Answers