Excel 2007 : Pivot Table Add All Fields

**reganjohnh** · 10-02-2009, 06:53 PM

Thanks for the reply....all of the fields are data fields containing statistics for a sports team. To be specific, the stats are captured on a per game basis, and I am capturing them on a per player basis. So my pivot table takes each stat (column) and sums up the column to give me a per play season stat.

To answer your question, I believe that each column is a data field. My problem literally is I have to check off each box in the pivot table wizard (284 times) multiple times a week which I feel there has to be a better way.

Also, I am using XL2007, I have switched since creating the profile.

**DonkeyOte** · 10-03-2009, 03:11 AM

Still slightly curious as your prior post would imply at least one Row Label field (player) but based on your request perhaps something along the lines of:

Public Sub PTDATAFIELDS()
Dim PT As PivotTable, lngField As Long
Application.ScreenUpdating = False
Set PT = Sheets("Sheet2").PivotTables("PivotTable1")
With PT
    For lngField = 1 To .PivotFields.Count Step 1
        .AddDataField(.PivotFields(lngField)).Function = xlSum
    Next lngField
End With
Set PT = Nothing
Application.ScreenUpdating = True
End Sub

Change that in red to reflect your actual setup - sheet name / PT name etc...

**reganjohnh** · 10-05-2009, 09:40 AM

You are 100% correct, I do have a row field which is player_id. I ran the sub you included below, but I received an error with this line:

.AddDataField(.PivotFields(lngField)).Function = xlSum

Any idea why that would throw an error? Thank you so much for the help.