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.

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