Excel 2007 : Pivot Table Add All Fields

**reganjohnh** · 10-02-2009, 01:03 PM

I have a pivot table that captures 284 columns, and I need to include all of the columns. I have to repeat this weekly, so I would like to find a way to "check off" all of the fields to be included in the pivot table without having to click 284 times, is this possible?

My other option is to use the "Sub Total" function, but I would prefer to use pivot tables if possible since I am more familiar. Even with the Sub Total it looked like I was running into the same issue of having to click every column to be totaled.

Help!

Thanks in advance.

**DonkeyOte** · 10-02-2009, 02:21 PM

You have 284 columns, ok... but... are all the fields to act as Data Fields ie no Row Fields / Columns Fields / Page Fields ?
(XL2007 read as Row Labels / Column Labels / Report Filters)

I presume also you must be using XL2007 given the # of columns in use - your profile implies otherwise (XL2003)

**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.

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

It looks like when I get to column KE it will no longer let me add fields to the pivot table.

**DonkeyOte** · 10-05-2009, 09:50 AM

Sorry, yes, restricted to 256 column fields.
(I didn't check on that volume of data)

**reganjohnh** · 10-05-2009, 11:07 AM

Thanks for all the help. It looks like I am going to have to use a combination of SUMIF, AVERAGEIF to arrive at the per player stats.

**DonkeyOte** · 10-05-2009, 11:09 AM

Given you're using 2007 note also:

SUMIFS / COUNTIFS / AVERAGEIF / AVERAGEIFS

The above are not available in earlier versions but can generally help keep calcs relatively efficient in XL2007 when compared to SUMPRODUCT and Arrays (often used in earlier versions (rightly/wrongly))

Excel 2007 : Pivot Table Add All Fields

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Pivot Table Add All Fields

Re: Pivot Table Add All Fields

Re: Pivot Table Add All Fields

Re: Pivot Table Add All Fields

Re: Pivot Table Add All Fields

Re: Pivot Table Add All Fields

Re: Pivot Table Add All Fields

Re: Pivot Table Add All Fields

Re: Pivot Table Add All Fields

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions