PDA

View Full Version : PivotTable not functioning



BeachBum
08-15-2016, 12:01 AM
Hi all,

I have read through several articles about this error and numerous posts about similar issues but simply cant make it apply to this macro.

The below code is part of a larger code that should create a pivot table on a new tab. Unfortunately it keeps returning the following error:

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

Can anyone with more experience than me identify what is wrong with the code? For the life of me I just cant see it!


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



Thanks

Aflatoon
08-15-2016, 03:32 AM
One of the field names must be wrong.

By the way, since you already have a variable referring to the pivot table, you could use PT rather than Sheets("PivotTable1").PivotTables("Occupancy")

BeachBum
08-15-2016, 05:28 AM
That was my first impression as well. I have copy and pasted the heading title into the code so i know that it is correct. I have also found a couple of other mentions of this occurring. For some reason when a pivot sub procedure is part of other sub procedures(it may occur under other circumstances, this is just when I found it to occur), 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.

Paul_Hossler
08-15-2016, 06:11 AM
can you post a small work book with a sample of the data that shows the problem?

Aflatoon
08-15-2016, 07:56 AM
believe it has something to do with refreshing the pivot and data.

Ah - are you querying the source data from somewhere else? If so, it may be that the query is still refreshing when you try to create the pivot table and the fields don't yet exist. If that is the case you should ensure that the query is not allowed to refresh in the background.

Also, you should really read the forum rules as regards cross-posting.