Pivot Table Report Filter Macros

Use macros to quickly change the report filters in an Excel pivot table, or block filter changes. Download free workbooks to test the macros.

Block Selection of (All) in Report Filter

In some pivot tables, you might want to prevent people from selecting the "(All)" option in a Report Filter. Use this macro to stop them from choosing that option.

Worksheet Formulas

In this example, the worksheet has GetPivotData formulas that refer to a Report Filter cell. The formulas work correctly if an order date is selected in cell B1. If (All) is selected, the formulas show a message -- "Select single order date".

Worksheet Macro

You can't remove the (All) option from the report filter's drop-down list, but you can use a macro to prevent users from selecting that option.

The code below, stored on the OrderDates sheet's module, can undo the report filter change, if (All) is selected or if multiple items are selected in the filter. The macro also shows a message warning -- "Please select a single date."

How the Block (All) Macro Works

When you select an item from a report filter's drop-down list, the PivotTableUpdate event is triggered. The code checks to see if the (All) item was selected. If it was, the code undoes the change to the report filter, and displays the previously selected item.

The Block (All) Macro Code

To use this macro, copy the code below, and paste it onto the pivot table's worksheet code module.

This code is also available in the Block (All) sample file that you can download.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim pf As PivotField
On Error GoTo exit_Handler

Application.EnableEvents = False
Application.ScreenUpdating = False

If ActiveSheet.Name = Me.Name Then
    For Each pf In Target.PageFields
        If pf.CurrentPage = "(All)" Then
            Application.Undo
            MsgBox "Please select a single date."
        End If
    Next pf
End If

exit_Handler:
    Set pf = Nothing
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub 

Scroll Through Report Filter Items

When you click the drop down arrow on a Report Filter, you can select one or more items to view in the pivot table. In Excel 2010, and later versions, you can also use Slicers to select items from the Report Filter.

If you don't have room for Slicers on your worksheet, add a Spin Button, to quickly scroll through the items in a Report Filter.

How to Add a Spin Button

To use this technique, follow these steps to add a Spin Button on the pivot table worksheet.

1. On the Excel Ribbon, click the Developer tab
2. In the Controls group, click Insert
3. Under ActiveX Controls, click the Spin Button
4. Draw a short, wide Spin Button, near the Report Filter field.
5. Right-click the Spin Button, and click View Code
6. A Change event procedure is created for the Spin Button. From the drop down list at the top of the code window, select SpinDown, and then select SpinUp, to create two more events.
7. In the SpinUp procedure, type this line of code: PivotPageUp
8. In the SpinDown procedure, type this line of code: PivotPageDown
9. If a Change procedure was created, you can delete it.

Test the Code

Before you can test the Pivot Spinner button, copy the additional macros in the next section, and paste the into your workbook.

Then, follow these steps, to test the code:

1. Go to the Developer tab on the Ribbon, and click the Design Mode command, to exit Design Mode.
2. Click the Up or Down arrow on the Spin Button, to scroll through the Report Filter items.

NOTE: This will only change the first Report Filter, if there are multiple Report Filter fields.

Pivot Spinner Macro Code

Copy the following macro code, and paste it into a regular code module in the workbook. There are two macros in the code -- PivotPageUp and PivotPageDown.

Sub PivotPageUp()
' Developed by Contextures Inc.
' www.contextures.com
Dim intPI As Integer
Dim i As Integer
Dim pt As PivotTable
Dim pf As PivotField
Dim strPI As String
Dim pi As PivotItem

Set pt = ActiveSheet.PivotTables(1)
Set pf = pt.PageFields(1)
strPI = pf.CurrentPage.Name
intPI = 1

'get the current item number
For Each pi In pf.PivotItems
  If pf.CurrentPage.Name = "(All)" Then
    intPI = 0
    Exit For
  End If
  If pi.Name = strPI Then
      Exit For
  End If
  intPI = intPI + 1
Next pi

i = 1
'show the next visible item
For i = intPI + 1 To pf.PivotItems.Count + 1
  On Error Resume Next
  If i = pf.PivotItems.Count + 1 Then
    pf.CurrentPage = "(All)"
    Exit For
  End If
  pf.CurrentPage = pf.PivotItems(i).Name
    If Err.Number = 0 Then
      Exit For
    End If
Next i

End Sub
'===============================================
Sub PivotPageDown()
' Developed by Contextures Inc.
' www.contextures.com
Dim intPI As Integer
Dim i As Integer
Dim pt As PivotTable
Dim pf As PivotField
Dim strPI As String
Dim pi As PivotItem

Set pt = ActiveSheet.PivotTables(1)
Set pf = pt.PageFields(1)
strPI = pf.CurrentPage.Name
intPI = 1

'get the current item number
For Each pi In pf.PivotItems
  If pf.CurrentPage.Name = "(All)" Then
    intPI = pf.PivotItems.Count + 1
    Exit For
  End If
  If pi.Name = strPI Then
      Exit For
  End If
  intPI = intPI + 1
Next pi

'show the previous visible item
For i = intPI - 1 To 0 Step -1
  On Error Resume Next

  pf.CurrentPage = pf.PivotItems(i).Name
    If Err.Number = 0 Then
      Exit For
    End If
  If i = 0 Then
    pf.CurrentPage = "(All)"
    Exit For
  End If
Next i

End Sub

Scroll Through Table Filter Items

This technique is based on the Pivot Table scrolling example, shown above. However, it also filters a column in the Excel table on which the pivot table is based.

In this animated screen shot, the Product field is filtered, when the Spin Button arrows are clicked.

This example is in the Download section below -- look for the sample named Pivot Spinner Table Filter.

How to Set Up the Spin Button Filter

To set this up, follow all of the instructions in the Scroll Through Pivot Filter section, above.

Then, add the following macro (ChangeFilter) to your workbook, in a regular code module. This code gets the name of the current item selected in the pivot table, and selects that item in the table's Product column.

Sub ChangeFilter()
Dim pt As PivotTable
Dim pf As PivotField
Dim tbl As ListObject
Dim ItemName As String
Dim FieldNum As Long

Set pt = ActiveSheet.PivotTables(1)
Set pf = pt.PageFields("Product")
Set tbl = ActiveSheet.ListObjects(1)

FieldNum = 3 'field location in table
ItemName = pf.CurrentPage

If ItemName = "(All)" Then
  tbl.Range.AutoFilter _
    Field:=FieldNum
Else
  tbl.Range.AutoFilter _
    Field:=FieldNum, _
    Criteria1:=ItemName
End If

End Sub

Change the Spin Button Code

The final step is to add the ChangeFilter macro name to the Spin Button code.

Type the ChangeFilter macro name in the SpinDown procedure, and in the SpinUp procedure.

Change Related Filter

This macro prevents the pivot table data from disappearing, when the Report Filter selections are changed. There are related filters -- Quarters and Months. This technique could be used for other types of related filters, such as Category and Product, or Region and City.

What the Change Related Filter Macro Does

In this sample code, when you select a month from the report filter, the Quarters filter automatically changes, to show the quarter for the selected month. Otherwise, all the values might temporarily disappear from the pivot table, if the selected quarter did not match the selected month.

If you select a different Quarter, the Months filter automatically changes to "(All)".

The Change Related Filter Macro Code

To add this code to your workbook, copy the code below, and paste it onto your pivot table's worksheet code module.

If necessary, change the field names in the code, to match the field names in your pivot table.

Option Explicit
Dim mvPivotMonth As Variant
Dim mvPivotQtr As Variant

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim pt As PivotTable
Dim pfQ As PivotField
Dim pfM As PivotField

On Error GoTo errHandler
Set pt = Target
Set pfQ = pt.PivotFields("Quarters")
Set pfM = pt.PivotFields("Months")

If LCase(pfQ.CurrentPage) _
      <> LCase(mvPivotQtr) Then
    Application.EnableEvents = False
    pt.ManualUpdate = True
      pfM.ClearAllFilters
      pfM.CurrentPage = "(All)"
    pt.ManualUpdate = False
    mvPivotQtr = pfQ.CurrentPage
    mvPivotMonth = pfM.CurrentPage
    GoTo exitHandler
End If

If LCase(pfM.CurrentPage) _
      <> LCase(mvPivotMonth) Then
    Application.EnableEvents = False
    pt.ManualUpdate = True
      pfQ.ClearAllFilters
      Select Case pfM.CurrentPage
        Case "Jan", "Feb", "Mar"
            pfQ.CurrentPage = "Qtr1"
        Case "Apr", "May", "Jun"
            pfQ.CurrentPage = "Qtr2"
        Case "Jul", "Aug", "Sep"
            pfQ.CurrentPage = "Qtr3"
        Case "Oct", "Nov", "Dec"
            pfQ.CurrentPage = "Qtr4"
        Case Else
            pfQ.CurrentPage = "(All)"
      End Select
    pt.ManualUpdate = False
    mvPivotMonth = pfM.CurrentPage
    mvPivotQtr = pfQ.CurrentPage
End If

exitHandler:
Set pt = Nothing
Set pfQ = Nothing
Application.EnableEvents = True
Exit Sub

errHandler:
  MsgBox "Could not update fields"
  Resume exitHandler

End Sub

Pivot Sheet for Each Filter Item

There is a built-in pivot table command that will create a separate copy of the pivot table, for each item in a Report Filter field. This macro automates that command, and adds a prefix to each sheet's name. That makes the sheets easy to locate and delete later.

Workbook Setup

In this example, a pivot table is on a worksheet named "SalesPivot". The "Rep" field is in the pivot table's Report Filter area.

This macro creates a copy of the "SalesPivot" sheet for each Sales Rep whose name is included in the Rep field. The new sheet is named for the Sales Rep, with a "PT_" prefix. Then, in the pivot table on the new sheet, that sales rep's name is selected in the Report Filter.

Pivot Sheet for Each Filter Item Macro Code

To use this code, copy it onto a regular code module in your workbook. Thc code is also available in the Sheet for Each Item sample file, in the download section below.

Sub CopyPivotPages()
'pivot table tutorial by contextures.com
 'creates sheet with copy of pivot table
 'for each item in specified page field
 'pivot item is selected on inserted page pivot table
 
On Error GoTo errHandler
Application.DisplayAlerts = False

Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim strPF As String
Dim strPI As String

Set ws = Sheets("SalesPivot")
Set pt = ws.PivotTables(1)
Set pf = pt.PageFields(1)
strPF = pf.Name

For Each pi In pf.PivotItems
  strPI = Left("PT_" & pi.Name, 31)
  On Error Resume Next
    Sheets(strPI).Delete
  On Error GoTo 0
  ws.Copy After:=Sheets(Sheets.Count)
  With ActiveSheet
    .Name = strPI
    With .PivotTables(1).PivotFields(strPF)
      .PivotItems(pi.Name).Visible = True
      .CurrentPage = pi.Name
    End With
  End With
Next pi

ws.Activate

exitHandler:
  Application.DisplayAlerts = True
  Exit Sub
errHandler:
  MsgBox "Could not create sheets"
  Resume exitHandler

End Sub

Sort All Report Filter Fields

Sometime the items in a pivot field get out of alphabetical order -- usually if new items have been added to the data, and they appear at the end of the list. This macro will sort the items in each Report Filter.

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.

Manual Sorting

You can easily sort the pivot fields in the row or column areas, but there isn't a quick way to sort the report filter fields. With a few steps, you can manually sort the report filter fields, by moving them to the row area, and then moving them back.

Automatic Sorting

If you would prefer to sort report filter fields automatically, you can use the following VBA code. This code is stored on a regular code module, and sorts all report fields in the first pivot table on the active sheet

Sort Report Filters Macro Code

To use this code in your workbook, copy the code below into a regular code module. The code is also available in the Sort Report Filters sample file

Sub SortReportFilters()
'downloaded from www.contextures.com
'sorts all report filter fields
'in first pivot table
'on active sheet
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim lPF As Long
Dim lCount As Long
Dim lPos As Long
Set ws = ActiveSheet
Set pt = ws.PivotTables(1)

lPF = pt.PageFields.Count
For lCount = 1 To lPF
  Set pf = pt.PageFields(lCount)
  lPos = pf.Position

  With pf
    .Orientation = xlRowField
    .AutoSort xlAscending, pf.Name
    .Orientation = xlPageField
    .Position = lPos
  End With
Next lCount
End Sub

Download the Workbooks

Block (All) -- Download the Block All sample file, which contains the pivot table and VBA code. The zipped file is in xlsm format, and contains macros. To test the code, enable macros when you open the file. In the workbook, to see the code, right-click the OrderDates sheet tab, and click View Code. To test the code, select (All) from the OrderDate report filter's drop-down list. You should see the warning message.

Pivot Spinner -- Download the Pivot Spinner sample file, which has a spin button to change the item selected in the Report Filter. The zipped file is in xlsm format, and contains macros. To test the code, enable macros when you open the file. In the workbook, to see the sheet code, right-click the SalesPivot sheet tab, and click View Code.

Pivot Spinner Table Filter -- Download the Pivot Spinner Table Filter sample file, which has a spin button to change the item selected in the Report Filter, and that changes the filter applied in the worksheet table too. The zipped file is in xlsm format, and contains macros. To test the code, enable macros when you open the file. In the workbook, to see the sheet code, right-click the FoodSales sheet tab, and click View Code.

Change Related Filter -- Download the Change Filters in Same Pivot Table sample file, which contains the pivot table and VBA code. The zipped file is in xlsm format, and contains macros. To test the code, enable macros when you open the file. In the workbook, to see the code, right-click the pivot table's sheet tab, and click View Code.

Sheet for Each Item -- Download the Pivot Sheet for Each Item sample file, which contains the pivot table and VBA code. The zipped file is in xlsm format, and contains macros. To test the code, enable macros when you open the file. The code is stored in a regular code module.

Sort Report Filters -- Download the Sort All Report Filter Fields sample file. To see the code, right-click the button on the Sales Pivot sheet, and click Assign Macro, then click Edit.

Pivot Table Tools

To save time when building, formatting and modifying your pivot tables, use the tools in my Pivot Power Premium add-in. With just a few clicks, you can:

• copy the formatting from one pivot table, and apply it to another pivot table.
• change all the values from Count to Sum
• remove the "Sum of" from all the headings

and much more!

More Tutorials

Pivot Table Report Filters

FAQs - Pivot Tables

Pivot Table Introduction

Running Totals

Summary Functions

Clear Old Items in Pivot Table