Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using this Automatic Resizing Macro with Worksheet Change | Excel Discussion (Misc queries) | |||
Event Macro adjustment needed - need to change font color also | Excel Worksheet Functions | |||
How do I change a Macro to use relative cell addresses? | Excel Discussion (Misc queries) | |||
Where to stick macro to change default comment font? | Excel Discussion (Misc queries) | |||
Macro button colour change??? | Excel Worksheet Functions |