Macro 68: Set All Data Items to Sum
When creating a PivotTable, Excel, by default, summarizes your data by either counting or summing
the items. The logic Excel uses to decide whether to sum or count the fields you add to your
PivotTable is fairly simple. If all of the cells in a column contain numeric data, Excel chooses to Sum.
If the field you are adding contains a blank or text, Excel chooses Count.
Although this seems to make sense, in many instances, a pivot field that should be summed legiti-
mately contains blanks. In these cases, we are forced to manually go in after Excel and change the
calculation type from Count back to Sum. That’s if we’re paying attention! It’s not uncommon to miss
the fact that a pivot field is being counted instead of summed up.
The macro in this section aims to help by automatically setting each data item’s calculation type
to Sum.
How it works
This macro loops through each data field in the PivotTable and changes the Function property to
xlSum. You can alter this macro to use any one of the calculation choices: xlCount,
xlAverage, xlMin, xlMax, and so on. When you go into the code window and type
pf.Function =, you see a drop-down list showing you all your choices (see Figure 6-3).
Figure 6-3: Excel helps out by showing you your enumeration choices.
Sub Macro68()
‘Step 1: Declare your Variables
Dim pt As PivotTable
Dim pf As PivotField
‘Step 2: Point to the PivotTable in the active cell
On Error Resume Next
Set pt = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)
‘Step 3: Exit if active cell is not in a PivotTable
If pt Is Nothing Then
MsgBox “You must place your cursor inside of a
PivotTable.” Exit Sub
End If
‘Step 4: Loop through all pivot fields apply SUM
For Each pf In pt.DataFields
pf.Function = xlSum
Next pf
End Sub
1. Step 1 declares two object variables. It uses pt as the memory container for the PivotTable
and pf as a memory container for the data fields. This allows us to loop through all the data
fields in the PivotTable.
2. This macro is designed so that we infer the active PivotTable based on the active cell. The
active cell must be inside a PivotTable for this macro to run. The assumption is that when the
cursor is inside a particular PivotTable, we want to perform the macro action on that pivot.
Step 2 sets the pt variable to the name of the PivotTable on which the active cell is found.
We do this by using the ActiveCell.PivotTable.Name property to get the name of
the target pivot.
If the active cell is not inside of a PivotTable, an error is thrown. This is why we use the On
Error Resume Next statement. This tells Excel to continue with the macro if there is an
error.
3. Step 3 checks to see if the pt variable is filled with a PivotTable object. If the pt variable is
set to Nothing, the active cell was not on a PivotTable, thus no PivotTable could be
assigned to the variable. If this is the case, we tell the user in a message box, and then we exit
the procedure.
4. If the macro has reached Step 4, it has successfully pointed to a PivotTable. It uses a
For Each statement to iterate through each data field. Each time a new pivot field is
selected, it alters the Function property to set the calculation used by the field. In this case,
we are setting all the data fields in the PivotTable to Sum.
After the name has been changed, we move to the next data field. After all the data fields
have been evaluated, the macro ends.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code.
