Pivot Table Programming

[Marvin]

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]

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