Macro 71: Apply Custom Sort to Data Items
On occasion, you may need to apply a custom sort to the data items in your PivotTable. For instance,
if you work for a company in California, your organization may want the West region to come before
the North and South. In these types of situations, neither the standard ascending nor descending
sort order will work.
How it works
You can automate the custom sorting of your fields by using the Position property of the
PivotItems object. With the Position property, you can assign a position number that specifies
the order in which you would like to see each pivot item.
In this example code, we first point to the Region pivot field in the Pvt1 PivotTable. Then we list
each item along with the position number indicating the customer sort order we need.
Sub Macro71()
With Sheets(“Sheet1”).PivotTables(“Pvt1”).PivotFields(“Region “)
.PivotItems(“West”).Position = 1
.PivotItems(“North”).Position = 2
.PivotItems(“South”).Position = 3
End With
End Sub
As brilliant as this option is, custom lists do not travel with your workbook, so a macro
helps in cases where it’s impractical to expect your clients or team members to set up their
own custom sort lists.
How to use it
You can implement this kind of a macro in 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.
