Pivot table "Sum of" or "Count of"

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

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
 
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
'===============
 
Back
Top