Pivot Table Field Settings

**droach** · 10-27-2009, 02:22 PM

I have a ton of data fields that contain some blank cells, which cause the field to use "Count" as the default. I need it to Sum and resetting each field is taking forever. Is there a way to change the default to "Sum" or change the setting to more than one field at a time. (When I try to change multiple field settings, only the first one is changed.)

Thanks,
droach

**DonkeyOte** · 10-27-2009, 03:54 PM

Assuming all the fields already exist in the Data Field section of your PT

Code:

Public Sub SET_PTFIELDS_TO_SUM()
Dim PT As PivotTable, PTField As PivotField
Set PT = ActiveSheet.PivotTables(1)
For Each PTField In PT.DataFields
    PTField.Function = xlSum
Next PTField
Set PT = Nothing
End Sub

alter that above in red as appropriate

**droach** · 10-27-2009, 05:26 PM

Thanks for the reply. I'm a moderate Excel user...

Is what you posted the code for a macro I can run? I tried to run it in the VB editor but is keeps saying that I need an "object". Any ideas?

Thanks in advance for your help.

droach

**DonkeyOte** · 10-28-2009, 04:22 AM

On what sheet is the Pivot Table resident ? What is the Pivot Table called ?

**droach** · 10-28-2009, 12:28 PM

The sheet is called "Pivot" and the work book is called "Buyer_Recap_DR1
". I don't think I named the actual Pivot Table. Is there a default? Where would I find the name?

Thanks,
droach