Macro to change the PivotField to sum
 

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

Macro to change the PivotField to sum
 

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.



Vick wrote:

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

Macro to change the PivotField to sum
 

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" wrote:

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.



Vick wrote:

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

Macro to change the PivotField to sum
 

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

Vick wrote:

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" wrote:

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.



Vick wrote:

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


--

Dave Peterson