Change pivot fields en masse

Topic

New Reply

I use many pivot tables and am always forced to change count fields to sum fields one-by-one, and having to format those sum fields in the kind ,0 numeric format. I recorded a macro for one table’s fields that had already been changed from count to sum, but I’m not sure how to generalize such a macro. So how would a macro look that says “for all data fields, change them to sum of and change the format to ,0, for instance? thanks.

Reply | Quote

Replies

Does this do what you want? You must click somewhere in the pivot table before running the macro.

Code:

Sub FormatPivotData()
  Dim pt As PivotTable
  Dim pf As PivotField
  Set pt = Selection.PivotTable
  For Each pf In pt.DataFields
	pf.Function = xlSum
	pf.NumberFormat = ".0"
  Next pf
End Sub

Reply | Quote

Seems to work great! Thanks much, a real timesaver.

Reply | Quote