Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table Programming
I want to programmatically change all "Sum of" fields in my pivot table to
"Average of" fields. For example, I have a field called cquire. The following code will show Acquire, but not Sum of Acquire. Any help would be appreciated. Sub PivotFields() For Each pf In ActiveSheet.PivotTables("Pivottable2").PivotFields On Error Resume Next pf.ShowAllItems = True Debug.Print pf.Name, pf.Caption, pf.SourceName Next pf End Sub produces this output Date Date Date Action Action Action Budget Budget Budget Acquire Acquire Acquire Dispense Dispense Dispense Comments Comments Comments Net Net Net Data Data Error 2042 The following macro successfully accesses SUm of Acquire Sub Pivot() Debug.Print ActiveSheet.PivotTables("PivotTable2").PivotFields ("Sum of Acquire").Name _ , ActiveSheet.PivotTables("PivotTable2").PivotFields ("Sum of Acquire").Caption _ , ActiveSheet.PivotTables("PivotTable2").PivotFields ("Sum of Acquire").SourceName End Sub producing the following output Sum of Acquire Sum of Acquire Acquire Any help would be appreciated. Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table Programming
I'm not sure why you're trying to change the source name. The following
code will change any SUM field in the data area to an Average: '======================= Sub ChangePivotFunction() Dim pf As PivotField For Each pf In ActiveSheet.PivotTables(1).DataFields If pf.Function = xlSum Then pf.Function = xlAverage End If Next pf End Sub '======================== Marvin wrote: I want to programmatically change all "Sum of" fields in my pivot table to "Average of" fields. For example, I have a field called cquire. The following code will show Acquire, but not Sum of Acquire. Any help would be appreciated. Sub PivotFields() For Each pf In ActiveSheet.PivotTables("Pivottable2").PivotFields On Error Resume Next pf.ShowAllItems = True Debug.Print pf.Name, pf.Caption, pf.SourceName Next pf End Sub produces this output Date Date Date Action Action Action Budget Budget Budget Acquire Acquire Acquire Dispense Dispense Dispense Comments Comments Comments Net Net Net Data Data Error 2042 The following macro successfully accesses SUm of Acquire Sub Pivot() Debug.Print ActiveSheet.PivotTables("PivotTable2").PivotFields ("Sum of Acquire").Name _ , ActiveSheet.PivotTables("PivotTable2").PivotFields ("Sum of Acquire").Caption _ , ActiveSheet.PivotTables("PivotTable2").PivotFields ("Sum of Acquire").SourceName End Sub producing the following output Sum of Acquire Sum of Acquire Acquire Any help would be appreciated. Thanks. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to create pivot table from existing pivot table in excel 2007 | Excel Discussion (Misc queries) | |||
Excel Food Price Table, Programming, Is This Table All Right? | Excel Discussion (Misc queries) | |||
Pivot Table Programming | Excel Programming | |||
Pivot table Programming | Excel Programming | |||
VBA Programming with Pivot Tables | Excel Programming |