Pivot Table Report Filter Sort Macro Saves Time

When a pivot field is in the row or column area, you can sort the pivot items in that fields, using the built-in commands. For fields in the Report Filter area, there isn’t an easy way to sort the pivot items. I’ll show you how to sort manually, or use the pivot table report filter sort macro below to do the job quickly and easily

Row and Column Sorting

Here’s a screen shot of a pivot field named City, which is in the Row area of a pivot table. Currently, the city names are not in alphabetical order. To sort them:

Click the arrow in the field heading
Choose one of the Sort options, such as Sort A to Z

Read more about Pivot Table Sorting on my Contextures website.

Report Filter Fields

If I move the same field to the Report Filter area, the list of cities appears, for filtering, but there are no Sort options listed.

Read more about Report Filters on my Contextures website.

Sort a Report Filter Field Manually

If you just have one or two Report Filter fields to sort, follow these steps to manually sort them.

Temporarily move the field to the Row or Column area
Use the Sort options in the Row or Column field heading
Drag the field back to the Report Filter area

It’s not the ideal solution, certainly, but it works well, and is a good solution for a quick one-off situation.

This short video shows the manual steps.

Use a Report Filter Sort Macro

If you have to sort the Report Filter fields frequently, or if you have several of them to sort, a macro will make the job much quick and easier.

Copy the macro code from the next section, and paste it into a regular code module in your Excel workbook.

Note: If you have a copy of my Pivot Power Premium add-in, go to Filter, and click Sort Report Filters (see the screen shot below). That will sort all the Report Filter fields in all pivot tables on the active sheet.

Report Filter Sort Macro Message

This macro will show a message that asks if you want to temporarily collapse the Row Fields. That will help prevent the pivot table from running out of rows, if the pivot table is large, and one or more of the Report Filter fields contains a high number of pivot items.

Click Yes, unless you have some of the row fields already collapsed, and you don’t want to lose that layout.

Report Filter Sort Macro Code

Copy the following code to a regular code module in your workbook. Then, select a sheet that contains a pivot table, and run the macro.

For more Report Filter macros, visit my Contextures website.

Sub SortReportFilterFields()
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pfRow01 As PivotField
Dim lRptPos As Long
Dim lDrill As Long

On Error Resume Next
Set pt = ActiveSheet.PivotTables(1)
Set pfRow01 = pt.RowFields(1)

If pt.RowFields.Count > 1 Then
  lDrill = MsgBox("Collapse Row Fields temporarily?", _
    vbQuestion + vbYesNo + vbDefaultButton1, "Collapse")
End If

If lDrill = vbYes Then
  pfRow01.DrillTo pfRow01.Name
End If

pt.ManualUpdate = True

For Each pf In pt.PageFields
  lRptPos = pf.Position
  pf.DrillTo pf.Name
  pf.Orientation = xlRowField
  pf.AutoSort xlAscending, pf.SourceName
  pf.Orientation = xlPageField
  pf.Position = lRptPos
  pf.ShowDetail = True
Next pf

If lDrill = vbYes Then
  pfRow01.ShowDetail = True
End If

pt.ManualUpdate = False
MsgBox "Report Filters have been sorted"

End Sub

___________________________

Save