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