countingapples
Board Regular
- Joined
- Feb 20, 2003
- Messages
- 243
I created a macro to automate the pivot table process, however, it hangs up when I try to run it.
When it hangs up, I receive the following error message:
Run time error '1004':
Unable to get the PivotFields property of the PivotTable Class.
What happened and how do I fix it?
Code:
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!C1:C6").CreatePivotTable TableDestination:="", TableName:= _
"WarehousePivot", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("WarehousePivot").PageFieldWrapCount = 1
ActiveSheet.PivotTables("WarehousePivot").PivotCache.RefreshOnFileOpen = True
With ActiveSheet.PivotTables("WarehousePivot").PivotFields("Acctg Date") <---- This is where it highlights and hangs up.
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("WarehousePivot").PivotFields("Prj/Grt")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("WarehousePivot").PivotFields("CC/ Org")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("WarehousePivot").AddDataField ActiveSheet.PivotTables( _
"WarehousePivot").PivotFields("$ Amount"), "Count of $ Amount", xlCount
ActiveWorkbook.ShowPivotTableFieldList = False
Range("B5:J19").Select
ActiveSheet.PivotTables("WarehousePivot").PivotFields("Count of $ Amount"). _
Function = xlSum
Range("B20:K20,K5:K19").Select
Selection.Font.Bold = True
Range("B5:K20").Select
Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
End Sub
When it hangs up, I receive the following error message:
Run time error '1004':
Unable to get the PivotFields property of the PivotTable Class.
What happened and how do I fix it?