software

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

Debra Dalgleish has an addin that you may like.
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:
gt;
gt; I'm trying to find/creat a macro that will automatically change the Field in
gt; my pivottable to Sum. The problem that I'm running into is when I have have
gt; a new field name or a new pivottable name. I can creat one where the
gt; pivottable and the PivotField are always the same but that really doesn't
gt; help me. Any help would be greatly appreciated.
gt;
gt; Thanks
gt; Vick

--

Dave Peterson

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

quot;Dave Petersonquot; wrote:

gt; Debra Dalgleish has an addin that you may like.
gt; www.contextures.com/xlPivotAddIn02.html
gt;
gt; The code is unprotected, so you could just extract that portion and include it
gt; your macro if you want.
gt;
gt;
gt;
gt; Vick wrote:
gt; gt;
gt; gt; I'm trying to find/creat a macro that will automatically change the Field in
gt; gt; my pivottable to Sum. The problem that I'm running into is when I have have
gt; gt; a new field name or a new pivottable name. I can creat one where the
gt; gt; pivottable and the PivotField are always the same but that really doesn't
gt; gt; help me. Any help would be greatly appreciated.
gt; gt;
gt; gt; Thanks
gt; gt; Vick
gt;
gt; --
gt;
gt; Dave Peterson
gt;

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 quot;There are no pivot tables on the active sheetquot;
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 gt; 0 Then
PivotCheck = True
End If

End Function

Vick wrote:
gt;
gt; I tried pulling out the code for the macro I want, but it appears I receive
gt; an error saying sub or function not displayed and it highlights the
gt; PivotCheck line. I'm not sure what this is calling.
gt;
gt; Vicks
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; Debra Dalgleish has an addin that you may like.
gt; gt; www.contextures.com/xlPivotAddIn02.html
gt; gt;
gt; gt; The code is unprotected, so you could just extract that portion and include it
gt; gt; your macro if you want.
gt; gt;
gt; gt;
gt; gt;
gt; gt; Vick wrote:
gt; gt; gt;
gt; gt; gt; I'm trying to find/creat a macro that will automatically change the Field in
gt; gt; gt; my pivottable to Sum. The problem that I'm running into is when I have have
gt; gt; gt; a new field name or a new pivottable name. I can creat one where the
gt; gt; gt; pivottable and the PivotField are always the same but that really doesn't
gt; gt; gt; help me. Any help would be greatly appreciated.
gt; gt; gt;
gt; gt; gt; Thanks
gt; gt; gt; Vick
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson