Sort an Excel PivotTable by Value in Ascending or Descending Order with VBA
Question details
The user needs an Excel VBA macro that sorts PivotTable row fields by the value field named Count of Reference # in ascending or descending order.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Automating value-based sorting in an existing PivotTable using VBA.
- Observed behavior
- The desired result is to order PivotTable row items by Count of Reference #, from lowest to highest or highest to lowest.
Confirm the worksheet name, the target PivotTable, and the exact caption of the value field, including spaces and punctuation. Use a desktop version of Excel with VBA support, and save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if you want to retain the macro.
Use PivotField.AutoSort to sort row fields by Count of Reference #
Apply a VBA macro to sort each row field in the target PivotTable by the specified value field.
PivotField.AutoSort is called on the row field whose items you want to reorder. Its sort-field argument identifies the value field used for comparison—in this case, Count of Reference #.
Open the workbook in Excel and click inside the target PivotTable. Check that Count of Reference # appears in the Values area of the PivotTable Fields pane. The example uses the first PivotTable on Sheet1; replace those references if your target is different.
Go to Developer > Visual Basic in Excel. In the Visual Basic Editor, select your workbook in Project Explorer, then choose Insert > Module.
Paste this code into the module: Sub SortPivotByReferenceCount() Dim pt As PivotTable Dim pf As PivotField Set pt = ThisWorkbook.Worksheets("Sheet1").PivotTables(1) For Each pf In pt.RowFields pf.AutoSort xlAscending, "Count of Reference #" Next pf End Sub Replace Sheet1 with the actual worksheet name and Count of Reference # with the exact value-field caption. ThisWorkbook refers to the workbook containing the macro.
Keep xlAscending to sort from lowest to highest, or replace it with xlDescending to sort from highest to lowest. In Excel, go to Developer > Macros, select SortPivotByReferenceCount, and click Run.
Work with Excel-format spreadsheet reports in WPS Office
For everyday spreadsheet reporting, WPS Office offers a free, lightweight alternative with a familiar interface and support for Excel formats, making migration straightforward. WPS Spreadsheet provides PivotTable tools for interactive analysis, but it is not a guaranteed replacement for this Excel VBA macro; VBA availability and compatibility depend on the WPS edition.
Frequently Asked Questions
How do I sort the PivotTable from highest to lowest?
In the VBA module, change xlAscending to xlDescending in the AutoSort call: pf.AutoSort xlDescending, "Count of Reference #". Then run the macro from Developer > Macros.
Can I sort only one PivotTable row field?
Yes. Replace the For Each loop with pt.PivotFields("Your Row Field").AutoSort xlAscending, "Count of Reference #". Replace Your Row Field with the exact name of the row field you want to sort.
Why does AutoSort fail when I use Count of Reference #?
Click the PivotTable and check the value-field caption in the PivotTable Fields pane or Value Field Settings. The macro must identify the actual value field; a renamed caption, different aggregation, or incorrect PivotTable reference can cause the call to fail.
How do I target a PivotTable other than the first one on the worksheet?
Click the target PivotTable and check its name on the PivotTable Analyze tab. Then use its name in the VBA reference, for example: Set pt = ThisWorkbook.Worksheets("Sheet1").PivotTables("PivotTable2").
Can I run this macro in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel, then use Developer > Visual Basic to add the code and Developer > Macros to run it.
