Pivot Table Report Filter Macros

Use macros to quickly change the report filters in an Excel pivot table, or block filter changes

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.

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".

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

The following code, 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."

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 

To test the code, select (All) from the OrderDate report filter's drop-down list. You should see the warning message.

How It 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.

Download the Block All Sample File

You can download the Block All sample file, which contains the pivot table and VBA code. The zipped file is in Excel 2007/2010 format, and you will have to enable macros when you open the file.

In the workbook, to see the code, right-click the OrderDates sheet tab, and click View Code.

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 have an earlier version of Excel, or don't have room for Slicers on your worksheet, you can add a Spin Button, to quickly scroll through the items in a Report Filter.

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. You can delete the Change procedure that was created.

Next, copy the following code, and paste it into a new module in the workbook:

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

Test the Code

To test the code, go to the Developer tab on the Ribbon, and click the Design Mode command, to exit Design Mode.

Then, click the Up or Down arrow on the Spin Button, to scroll through the Report Filter items. This will only change the first Report Filter, if there is more than one.

Download the Pivot Spinner Sample File

You can download the Pivot Spinner sample file, which contains the pivot table and VBA code. The zipped file is in Excel 2007/2010 format, and you will have to enable macros when you open the file.

In the workbook, to see the code, right-click the SalesPivot sheet tab, and click View Code.

Change Filters in Same Pivot Table

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. If you select a different Quarter, the Months filter automatically changes to "(All)".

This code is stored on the sheet module for the worksheet on which the pivot table is located.

Download the Change Filters in Same Pivot Table sample file.

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

New Sheet for Each Item in Report Filter

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 code 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.

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

strPF = "Rep"
Set ws = Sheets("SalesPivot")
Set pt = ws.PivotTables(1)
Set pf = pt.PivotFields(strPF)

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.

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

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..

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

More Tutorials

Pivot Table Report Filters

FAQs - Pivot Tables

Pivot Table Introduction

Running Totals

Summary Functions

Clear Old Items in Pivot Table