How to sort a field in a PivotTable using VBA?

Sorting a field in a PivotTable using VBA involves a few steps. Here’s a basic guide on how to accomplish this:

  • Access the PivotTable: First, you need to determine which PivotTable you want to sort.
  • Identify the Field to Sort: Decide which field or element within the PivotTable needs sorting.
  • Use VBA to Sort the Field: Write a VBA macro to sort the field in the desired order.

Here’s a sample code demonstrating how to sort a field in a PivotTable using VBA:

Sub SortPivotTableField()

    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    
    ' Set the worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")  ' Change Sheet1 to your sheet name
    
    ' Set the PivotTable
    Set pt = ws.PivotTables("PivotTable1")  ' Change PivotTable1 to your PivotTable name
    
    ' Set the PivotField you wish to sort
    Set pf = pt.PivotFields("FieldName")  ' Change FieldName to your field name
    
    ' Sort the field in ascending order
    pf.AutoSort xlAscending, pf.SourceName
    
    ' To sort the field in descending order, use:
    ' pf.AutoSort xlDescending, pf.SourceName

End Sub

Explanation:

  • `ws`: Represents the worksheet where the PivotTable resides.
  • `pt`: Denotes the PivotTable you’re targeting.
  • `pf`: Refers to the PivotField within the PivotTable that you want to sort.
  • `pf.AutoSort`: The method used to sort the field. It takes two arguments:
    • The first argument (`xlAscending` or `xlDescending`) sets the sort order.
    • The second argument (`pf.SourceName`) specifies which data to base the sort on.

Notes:

  • Make sure to replace `”Sheet1″`, `”PivotTable1″`, and `”FieldName”` with the actual names relevant to your worksheet, PivotTable, and field.
  • If your fields are hierarchical or have additional levels, you may need to adjust the VBA code to reference the appropriate subfields.

Running this VBA script will sort the specified field within your PivotTable in either ascending or descending order.

Unlock Your Potential

Excel

Basic - Advanced

Access

Access Basic - Advanced

Power BI

Power BI Basic - Advanced

Help us grow the project