error 1004: unable to get the pivotfields property of the pivot table class.
I get the error in the following function AddPivotTablesColumns, I am using to add the columns (is date range on months) to the pivot tables. The funny thing is that when I append data at the begging of a calendar it works but if I called to add the columns for months after the current calendar defined it fails and return error 1004: unable to get the pivotfields property of the pivot table class.
The error is adding a column in the pivot table where probably the sum is empty... ie.
ptRole.AddDataField ptRole.PivotFields(strDateFormat), sumTotal, xlSum
when is adding at the end of the calendar an the month doesnt contain data...
I got really no idea how to proceed, please advice!
Please advice, attached is function i call previously to remove the columns.
Private Function CleanPivotTablesColumns()
Dim pf As PivotField
Dim pt As PivotTable
Set pt = Worksheets("role").PivotTables("RoleTable")
pt.PivotCache.Refresh
For Each pf In pt.ColumnFields
pf.Orientation = xlHidden
Next pf
pt.PivotCache.Refresh
pt.RefreshTable
Set pt = Worksheets("level").PivotTables("LevelTable")
pt.PivotCache.Refresh
For Each pf In pt.ColumnFields
pf.Orientation = xlHidden
Next pf
pt.PivotCache.Refresh
pt.RefreshTable
Set pt = Worksheets("workforce").PivotTables("WorkforceTable")
pt.PivotCache.Refresh
For Each pf In pt.ColumnFields
pf.Orientation = xlHidden
Next pf
pt.PivotCache.Refresh
pt.RefreshTable
End Function
Private Function AddPivotTablesColumns()
Dim intTempCol As Integer
Dim counter As Integer
Dim strDateFormat As String
'Add fields in Pivot tables
Dim ptRole As PivotTable
Dim ptLevel As PivotTable
Dim ptWorkforce As PivotTable
Dim sumTotal As String
Set ptRole = Worksheets("role").PivotTables("RoleTable")
Set ptLevel = Worksheets("level").PivotTables("LevelTable")
Set ptWorkforce = Worksheets("workforce").PivotTables("WorkforceTable")
'Pivote table refresh
ptRole.PivotCache.Refresh
ptRole.RefreshTable
ptLevel.PivotCache.Refresh
ptLevel.RefreshTable
ptWorkforce.PivotCache.Refresh
ptWorkforce.RefreshTable
intTempCol = Range("data_last_fixed_col").Column + 1
While (Cells(Range("data_header_row").Row, intTempCol) <> "")
strDateFormat = Trim(Format(Cells(Range("data_header_row").Row, intTempCol), "mmm-yy"))
sumTotal = "Sum of " & strDateFormat
ptRole.AddDataField ptRole.PivotFields(strDateFormat), sumTotal, xlSum
With ptRole.PivotFields(sumTotal)
.NumberFormat = "0.0"
End With
ptLevel.AddDataField ptLevel.PivotFields(strDateFormat), sumTotal, xlSum
With ptLevel.PivotFields(sumTotal)
.NumberFormat = "0.0"
ptWorkforce.AddDataField ptWorkforce.PivotFields(strDateFormat), sumTotal, xlSum
With ptWorkforce.PivotFields(sumTotal)
.NumberFormat = "0.0"
End With
intTempCol = intTempCol + 1
Wend
'Pivote table refresh
ptRole.PivotCache.Refresh
ptRole.RefreshTable
ptLevel.PivotCache.Refresh
ptLevel.RefreshTable
ptWorkforce.PivotCache.Refresh
ptWorkforce.RefreshTable
Set ptRole = Nothing
Set ptLevel = Nothing
Set ptWorkforce = Nothing
End Function
Re: error 1004: unable to get the pivotfields property of the pivot table class.
I' LOVE to see the awnser to this as well.
Re: error 1004: unable to get the pivotfields property of the pivot table class.
Julie, This is an old thread... If you are facing the same problem then I would advice you to start a new thread... post a sample workbook if possible... so that we can help you :)