Changing a field setting in a pivot table

T

The Rook

I have a pivot table which contains approx 300 columns which have all
defaulted to 'COUNT' I am wanting to change them ALL to sum, but it onlys
seems to let me do it one by one.

Is there anyway of doing it in bulk?
 
R

Roger Govier

Hi

The default in the PT is COUNT, if data in the column is Text or Blank.
I suspect you have defined a very large range for your PT, to allow for
future data entry, which would then cause the PT to default to Count.
If you create a Dynamic Range and give this as the source, then that should
get rid of your problem

Insert>Name>Define> Name myData refers to
=$A$1:INDEX($1:$65536,COUNTA($A:$A),COUNTA($1:$1))
For the PT, set Source=myData

To change all the fields from Count to Sum, then use the following code
(from Debra Dalgleish)
Sub ChangetoSum()
Dim pt As PivotTable, pf As PivotField, ws As Worksheet, i As Long
Application.ScreenUpdating = False
Set ws = ActiveSheet
For i = 1 To ws.PivotTables.Count
Set pt = ws.PivotTables(i)
pt.ManualUpdate = True
For Each pf In pt.DataFields
pf.Function = xlSum
Next
pt.ManualUpdate = False
Next i
Application.ScreenUpdating = True
End Sub

Copy the Code above
Alt+F11 to invoke the VB Editor
Insert>Module
Paste code into white pane that appears
Alt+F11 to return to Excel

To use
Select sheet containing the PT's
Alt+F8 to bring up Macros
Highlight ChangeToSum
Run
 
Top