error 1004: unable to get the pivotfields property of the pivot table class.-VBForums
Results 1 to 3 of 3

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

  1. #1

    Thread Starter
    New Member
    Join Date
    Jul 2007
    Posts
    3

    Unhappy 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

  2. #2
    Fanatic Member
    Join Date
    Aug 2005
    Location
    Up State NY
    Posts
    525

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

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

  3. #3
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,928

    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
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.