Changing all pivot table data fields to sum

Ever needed to change a large number of pivot table data fields from Count to Sum? I used to have a macro just for that in my personal.xls file but my office workstation was upgraded a few weeks ago and I had forgotten to keep a copy of the code. I found the code again at vbaexpress.com and amended it slightly so that it applies to the currently selected pivot table only:

Sub ChangePivotTableFieldsToSum()

Dim pt As PivotTable

On Error Resume Next
Set pt = ActiveCell.PivotCell.PivotTable
On Error GoTo 0
If pt Is Nothing Then
    MsgBox “No PivotTable selected”, vbInformation, “Oops…”
    Exit Sub
End If

For Each pf In pt.DataFields
    If Not pf Is Nothing Then
        With pf
            .Function = xlSum
        End With
    End If
Next pf

End Sub

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s