Hello, welcome to Macro Monday, and it is time for some more Macro fun.   Have you noticed that when you create a Pivot Table, Excel by default will either SUM or Count your items.  To read more details about why this occurs you can check out my blog post here.  But, today I want to show you how to set all of your Pivot Table data items to SUM.

If Excel decides to COUNT your items for example if you data contains text or blanks, if your data set is obviously numeric then SUM will be used.  If you want to avoid having to manually go into the Pivot Table and amend the COUNT back to SUM then this small piece of VBA is just the job.

Download Macro Mondays Example Files.

What Does The Macro Do?

The macro will automatically loop through and set each of the data items in your Pivot Table to SUM.  It uses the changes the Function property of the Pivot Table to SUM.  It assumes the user will have selected an active cell in a Pivot Table.

How Does It Work?

FIRST. Open Visual Basic – by hitting ALT +F11 or Developer Tab – Visual Basic – Insert Module

Step 1.  We have two variables to declare, this ensures  Excel creates a memory container for each of the Pivot Tables it will loop through.

Dim pt As Pivot Table

Dim ptf As PivotField

Step 2. This points to the Pivot Table that is associated with the active active cell selected by the user.

Step 3. Excel starts to loop through all of the Pivot Table Fields in the active Pivot Table and apply SUM to them, by altering the Function property.  After the first field has been changed then it moves to the next data field, once all fields have been evaluated the macro ends.

Step 4. Test Your Macro!.

You can download the code here or copy it from below.

Sub PivtToSum()

Dim pt As PivotTable
Dim ptf As PivotField

On Error Resume Next

Set pt = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)

    For Each ptf In pt.DataFields
    ptf.Function = xlSum
    Next ptf

End Sub