PivotFields property of the PivotTable Class problems

Question

I know I will be marked down for this but here it goes.

I have been going through several forums and informative websites regarding this error but I just cant work out what is wrong.

The error is:

Run-time error '1004': Unable to get the PivotFields property of the PivotTable Class

This occurs at the line: With Sheets("PivotTable1").PivotTables("Occupancy").PivotFields("Precinct")

I have seen reference that the error may be because the field is not called "Precinct". However I have copy and pasted it directly and have also ensured that the code "writes" that particular heading. I just can't figure it out. Could it be something to do with refreshing the data or pivot table? Is there a way to replace "Precinct" in the problem line with a cell reference?

The code is:

    Sub OccupancyPivot() 
Dim SrcData As Variant 
Dim LRow As Long, LCol As Long 
Dim wsSheet As Worksheet 
Dim PTCache As PivotCache 
Dim PT As PivotTable 

 'Determine the data range you want to pivot
LRow = Cells(Rows.Count, 1).End(xlUp).Row 
LCol = Cells(1, Columns.Count).End(xlToLeft).Column 
Set SrcData = Worksheets("Raw Data").Range("A1:" & Cells(LRow, LCol).Address(False, False)) 

Sheets.Add.Name = "PivotTable1" 

Set PTCache = ActiveWorkbook.PivotCaches.Add(xlDatabase, SrcData) 

Set PT = PTCache.CreatePivotTable(Sheets("PivotTable1").Range("A1"), "Occupancy") 

 'Create the headings and row and column orientation
With Sheets("PivotTable1").PivotTables("Occupancy").PivotFields("Precinct") 
    .Orientation = xlRowField 
    .Position = 1 
End With 
With Sheets("PivotTable1").PivotTables("Occupancy").PivotFields("Registration") 
    .Orientation = xlDataField 
    .Function = xlCount 
End With 

With Sheets("PivotTable1").PivotTables("Occupancy").PivotFields("Captured Date") 
    .Orientation = xlColumnField 
    .Position = 1 
End With 

With Sheets("PivotTable1").PivotTables("Occupancy").PivotFields("Captured Session") 
    .Orientation = xlColumnField 
    .Position = 2 
End With 

With Sheets("PivotTable1").PivotTables("Occupancy").PivotFields("Location") 
    .Orientation = xlRowField 
    .Position = 2 
End With 

 'ActiveWorkbook.Sheets("PivotTable").Visible = xlSheetVeryHidden
End Sub

Anyone able to tell me what is wrong with the above?

Edit: I have found a couple of other mentions of this occurring. For some reason when a pivot sub procedure is part of other sub procedures, the pivotfields don't recognize the headings in the data. I am yet to find a definitive reason for this but believe it has something to do with refreshing the pivot and data.

What does msgbox PT.PivotFields("Precinct").Caption return if you add it before the line causing the error? — Rory
@Rory When I place it before the With statements it just comes up with the error mentioned above. — user6661501
@Rory I also thought that to begin with. Even if I copy and paste the field name from the source data, the error still occurs. Apparently the pivot function doesn't recognize the names, but I can't find out why. — user6661501
Do you have the Raw Data sheet active when you start this code? — Rory

Shai Rado Shai Rado · Accepted Answer · 2016-08-15T10:42:14

Try the modified code below, I added also cases where you already created the "PivotTable1" sheet and "Occupancy" Pivot table (in previous runs of this code), and then you just want to refresh the Pivot's data with the modified data in "Raw Data" sheet.

Sub OccupancyPivot()

Dim SrcData             As Variant
Dim LRow                As Long, LCol       As Long
Dim wsSheet             As Worksheet
Dim PTCache             As PivotCache
Dim PT                  As PivotTable
Dim PivotShtExists      As Boolean

 ' Determine the data range you want to pivot
LRow = Worksheets("Raw Data").Cells(Worksheets("Raw Data").Rows.Count, 1).End(xlUp).Row
LCol = Worksheets("Raw Data").Cells(1, Worksheets("Raw Data").Columns.Count).End(xlToLeft).Column
Set SrcData = Worksheets("Raw Data").Range("A1:" & Cells(LRow, LCol).Address(False, False))

' check is "PivotTable1" sheet already exists (from previous Macro runs)
For Each wsSheet In ThisWorkbook.Sheets
    If wsSheet.Name = "PivotTable1" Then
        PivotShtExists = True
    End If
Next wsSheet

If Not PivotShtExists Then Sheets.Add.Name = "PivotTable1"
Set wsSheet = ThisWorkbook.Sheets("PivotTable1")

Set PTCache = ActiveWorkbook.PivotCaches.Add(xlDatabase, SrcData)

' add this line in case the Pivot table doesn't exit >> first time running this Macro
On Error Resume Next
Set PT = wsSheet.PivotTables("Occupancy") ' check if "Occupancy" Pivot Table already created (in past runs of this Macro)

On Error GoTo 0
If PT Is Nothing Then

    ' create a new Pivot Table in "PivotTable1" sheet, start from Cell A1
    Set PT = wsSheet.PivotTables.Add(PivotCache:=PTCache, TableDestination:=wsSheet.Range("A1"), TableName:="Occupancy")


     'Create the headings and row and column orientation
    With PT.PivotFields("Precinct")
        .Orientation = xlRowField
        .Position = 1
    End With
    With PT.PivotFields("Registration")
        .Orientation = xlDataField
        .Function = xlCount
    End With

    With PT.PivotFields("Captured Date")
        .Orientation = xlColumnField
        .Position = 1
    End With

    With PT.PivotFields("Captured Session")
        .Orientation = xlColumnField
        .Position = 2
    End With

    With PT.PivotFields("Location")
        .Orientation = xlRowField
        .Position = 2
    End With

Else
    ' just refresh the Pivot cache with the updated Range (data in Sheet1)
    PT.ChangePivotCache PTCache
    PT.RefreshTable
End If

 'ActiveWorkbook.Sheets("PivotTable").Visible = xlSheetVeryHidden
End Sub

After running this code, multiple scanarios, this is the result I am getting (simulated data):

PivotFields property of the PivotTable Class problems

2 Answers