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?

You could create your own macro or even use Debra Dalgleish's PivotPower Add-in:
http://contextures.com/xlPivotAddIn02.html#PivotItems



The Rook wrote:

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?

--

Dave Peterson

Thanks,

Worked perfectly

The Rook

"Dave Peterson" wrote:

You could create your own macro or even use Debra Dalgleish's PivotPower Add-in:
http://contextures.com/xlPivotAddIn02.html#PivotItems



The Rook wrote:

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?

--

Dave Peterson

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

InsertNameDefine 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
InsertModule
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


--
Regards
Roger Govier

"The Rook" wrote in message
...
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?