To add a custom sort order to a PivotTable using VBA, you need to specify the sort order explicitly for the items in the PivotTable field you want to sort. The process typically involves setting up a custom list in Excel, which the PivotTable can then use to sort its items. Here’s a step-by-step guide on how you can achieve this using VBA:
Step 1: Create a Custom List
Before you can use VBA to sort your PivotTable using a custom order, you first need to define that custom order in Excel itself.
- Go to `File` > `Options`.
- In the Excel Options dialog box, select `Advanced` from the left-hand menu.
- Scroll down to the `General` section and click on `Edit Custom Lists`.
- In the Custom Lists dialog box, manually enter the order you want in the `List entries` box and click `Add`. For example, if you have categories like “Medium”, “High”, and “Low”, enter them in the desired order.
- Click `OK` to close the dialog boxes.
Step 2: Use VBA to Sort the PivotTable
Now that you have your custom list set up, you can use VBA to apply this custom sort order to a PivotTable. Below is a basic example of how to do this:
Sub SortPivotTableCustomOrder()
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
' Set your worksheet and pivot table names accordingly
Set ws = ThisWorkbook.Worksheets("YourSheetName")
Set pt = ws.PivotTables("YourPivotTableName")
' Specify the field to sort
Set pf = pt.PivotFields("YourFieldName")
' Apply custom sort order
pf.AutoSort xlManual, pf.SourceName
pf.AutoSort xlAscending, pf.SourceName, UsingCustomList:=GetCustomList("Medium,High,Low")
' Refresh the PivotTable
pt.RefreshTable
End Sub
Function GetCustomList(customOrder As String) As Long
Dim tempRange As Range
Dim tempList() As String
' Split the customOrder string into an array
tempList = Split(customOrder, ",")
' Create a temporary range in the worksheet for the custom sort order
Set tempRange = ThisWorkbook.Sheets(1).Range("Z1:Z" & UBound(tempList) + 1)
tempRange.Value = Application.WorksheetFunction.Transpose(tempList)
' Convert the temporary range to a custom list and retrieve the list number
GetCustomList = Application.GetCustomListNum(tempRange)
' Clear the temporary range
tempRange.ClearContents
End Function
Explanation
- Worksheet and PivotTable References: Change `”YourSheetName”` and `”YourPivotTableName”` to match your worksheet and PivotTable names.
- PivotField Reference: Change `”YourFieldName”` to the field you want to sort.
- Custom Order: Replace `”Medium,High,Low”` in the `GetCustomList` function call with your custom sort order.
- Temporary Range: The code creates a temporary range to convert an array to a custom list, which allows Excel to use it for sorting.
Note
This script assumes that you don’t already have a custom sort list with the exact order you need. If you’ve already stored it, you don’t need the function and can simply use the list number in the `UsingCustomList` parameter.
Unlock Your Potential
Excel
Access
Power BI
