Run Time Error 1004 'Unable to get the PivotFields property of the PivotTable class

[wsnyder]

Hi all,

Using Excel365

My code is retuning an error

Run Time Error 1004 'Unable to get the PivotFields property of the PivotTable class'

Not sure why?
I am trying to add 4 Row Fields to the Pivot Table.
The first 3 are added to the Pivot, but not the 4th.

I see the column in the data source
I can manually drag the field from the Field List to the Rows Fields
Not adding the Column Fields or Data Fields either, but I suspect that is because of the Row Fields
What am I missing?

Thanks
-w

Private Sub AddFieldsToPivot(pt As PivotTable)
 
    'Error handler
        On Error GoTo ErrHandler
 
    'Add fields to pivot table
        With pt
       
            'Page Fields (Filters)
                .PivotFields("Vendor Name").Orientation = xlPageField
                .PivotFields("Vendor Name").Position = 1
               
                .PivotFields("Tracking Number").Orientation = xlPageField
                .PivotFields("Tracking Number").Position = 2
               
            'Column fields
                .PivotFields("Invoice Type").Orientation = xlColumnField
                .PivotFields("Invoice Type").Position = 1
           
            'Value fields
                .AddDataField .PivotFields("Invoice Amount"), _
                    Caption:="Sum of Invoice Amount", _
                    Function:=xlSum
           
            'Row fields
                .PivotFields("Global_ID").Orientation = xlRowField
                .PivotFields("Global_ID").Position = 1
               
                .PivotFields("Project Number").Orientation = xlRowField
                .PivotFields("Project Number").Position = 2
               
                .PivotFields("LOS").Orientation = xlRowField
                .PivotFields("LOS").Position = 3
               
                .PivotFields("Account Code").Orientation = xlRowField
                .PivotFields("Account Code").Position = 4
        End With
 
ErrHandler:
    If Err.Number > 0 Then _
        MsgBox Err.Description, vbMsgBoxHelpButton, "Get pivot table fields", Err.HelpFile, Err.HelpContext
        Err.Clear
 
End Sub

 

[sijpie]

Your function is called AddFieldstoPivot. So these PivotFields you are referring to, do they exist in the table, or do they need to be created (using pt.AddFields)?

 

[wsnyder]

Add using pt.pivotfields...

Thanks
-w