Macro to change the PivotField to sum

[Guest]

I'm trying to find/creat a macro that will automatically change the Field in
my pivottable to Sum. The problem that I'm running into is when I have have
a new field name or a new pivottable name. I can creat one where the
pivottable and the PivotField are always the same but that really doesn't
help me. Any help would be greatly appreciated.

Thanks
Vick

 

[Dave Peterson]

Debra Dalgleish has an addin that you may like.
http://www.contextures.com/xlPivotAddIn02.html

The code is unprotected, so you could just extract that portion and include it
your macro if you want.

 

[Guest]

I tried pulling out the code for the macro I want, but it appears I receive
an error saying sub or function not displayed and it highlights the
PivotCheck line. I'm not sure what this is calling.

Vicks

 

[Dave Peterson]

This is the code I meant (from the modData module):

Option Explicit

Sub SumAllData()
'changes data fields to SUM
On Error GoTo errHandler

Dim pt As PivotTable
Dim pf As PivotField
Dim ws As Worksheet
Set ws = ActiveSheet
Application.ScreenUpdating = False

If PivotCheck(ws) Then
For Each pt In ActiveSheet.PivotTables
pt.ManualUpdate = True
For Each pf In pt.DataFields
pf.Function = xlSum
Next pf
pt.ManualUpdate = False
Next pt
Else
MsgBox "There are no pivot tables on the active sheet"
End If

exitHandler:
Set pf = Nothing
Set pt = Nothing
Set ws = Nothing
Application.ScreenUpdating = True
Exit Sub

errHandler:
GoTo exitHandler
End Sub

'But make sure you include this portion, too (from the modPTCheck module):

Function PivotCheck(ws As Worksheet) As Boolean

PivotCheck = False

If ws.PivotTables.Count > 0 Then
PivotCheck = True
End If

End Function