CODEWITHSUNDEEP
excelpivot-tablevba
user6661501
user6661501

Reputation: 35

PivotFields property of the PivotTable Class problems

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.

Upvotes: 1

Views: 3955

Answers (2)

Shai Rado
Shai Rado

Reputation: 33692

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):

enter image description here

Upvotes: 0

Rory
Rory

Reputation: 34075

Based on what you've said so far, I think your LRow and/or Lcol variables are being set from the wrong sheet, so your source data isn't what it should be. Try this:

Sub OccupancyPivot()
    Dim SrcData               As String
    Dim wsSheet               As Worksheet
    Dim PTCache               As PivotCache
    Dim PT                    As PivotTable

    'Determine the data range you want to pivot
    SrcData = "'Raw Data'!" & Worksheets("Raw Data").Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1)

    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 PT
        With .PivotFields("Precinct")
            .Orientation = xlRowField
            .Position = 1
        End With
        With .PivotFields("Registration")
            .Orientation = xlDataField
            .Function = xlCount
        End With

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

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

        With .PivotFields("Location")
            .Orientation = xlRowField
            .Position = 2
        End With
    End With
    'ActiveWorkbook.Sheets("PivotTable").Visible = xlSheetVeryHidden
End Sub

Upvotes: 0

Related Questions