Macro 70: Sort All Fields in Alphabetical Order
If you frequently add data to your PivotTables, you may notice that new data doesn’t automatically
fall into the sort order of the existing pivot data. Instead, it gets tacked to the bottom of the existing
data. This means that your drop-down lists show all new data at the very bottom, whereas existing
data is sorted alphabetically.
How it works
This macro works to reset the sorting on all data fields, ensuring that any new data snaps into place.
The idea is to run it each time you refresh your PivotTable. In the code, we enumerate through each
data field in the PivotTable, sorting each one as we go.
Sub Macro70()
‘Step 1: Declare your Variables
Dim pt As PivotTable
Dim pf As PivotField
‘Step 2: Point to the PivotTable in the activecell
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 and sort
For Each pf In pt.PivotFields
pf.AutoSort xlAscending, pf.Name
Next pf
End Sub
1. Step 1 declares two object variables, using pt as the memory container for the PivotTable
and using pf as a memory container for our data fields. This allows the macro to loop
through all the data fields in the PivotTable.
2. 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.
In Step 2, we set 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 whether the pt variable is filled with a PivotTable object. If the pt vari-
able 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, the macro puts up a message box to notify the
user, and then exits the procedure.
4. Finally, we use a For Each statement to iterate through each pivot field. Each time a new
pivot field is selected, we use the AutoSort method to reset the automatic sorting rules for
the field. In this case, we are sorting all fields in ascending order. 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.
