Pivot table "Sum of" or "Count of"
 

I have created a macro to create several pivot tables. Some show "Sum of XXX"
and some "Count of XXX". I actually want all to be "Sum of XXX". So how to
change the macro to show "Sum of" when the pivot table gives "Count of". Here
are codes for one of the tables:
With pt1
.PivotFields("REGION").Orientation = xlPageField
.PivotFields("DIVISION").Orientation = xlColumnField
.PivotFields("YEAR").Orientation = xlRowField
.PivotFields("SALES").Orientation = xlDataField
End With

Thanks,
Scott

Pivot table "Sum of" or "Count of"
 

You can add a bit of code to change all the data fields to SUM:

'=================
Dim pf As PivotField
For Each pf In pt1.DataFields
pf.Function = xlSum
Next pf
'===============

Scott wrote:
I have created a macro to create several pivot tables. Some show "Sum of XXX"
and some "Count of XXX". I actually want all to be "Sum of XXX". So how to
change the macro to show "Sum of" when the pivot table gives "Count of". Here
are codes for one of the tables:
With pt1
.PivotFields("REGION").Orientation = xlPageField
.PivotFields("DIVISION").Orientation = xlColumnField
.PivotFields("YEAR").Orientation = xlRowField
.PivotFields("SALES").Orientation = xlDataField
End With

Thanks,
Scott



--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com