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