error 1004: unable to get the pivotfields property of the pivot table class.

[a0k0a7]

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

[juliemac]

I' LOVE to see the awnser to this as well.

[Siddharth Rout]

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