|
Home > Pivot > Filters > Macros Pivot Table Report Filter MacrosUse macros to quickly change the report filters in an Excel pivot table, or block filter changes. Download free workbooks to test the macros.
|
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.
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 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 in the filter. The macro also shows a message warning -- "Please select a single date."

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.
There are 2 versions of the macro below
1) Code for Normal Pivot Tables Only (not in Data Model)
2) Code for Normal or OLAP-Based Pivot Tables
To use this macro, copy the code below, and paste it onto the pivot table's worksheet code module.
This code is in the Block (All) - Normal Pivot sample file that you can download below.
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 use this macro, copy the code below, and paste it onto the pivot table's worksheet code module.
This code will work with Normal pivot tables (not in Data Model), or OLAP-based pivot tables (added to Data Model)
NOTE: This code is in the Block (All) OLAP and Normal sample file that you can download below.
That sample file has two pivot tables on the OrderDates sheet.
There are GetPivotData formulas to the right, which return data for the selected order date

There are GetPivotData formulas to the right, which return data for the selected order date

This code is on the OrderDates code module, and prevents selection of (All) for both types of pivot table.
Private Sub Worksheet_PivotTableUpdate _
(ByVal Target As PivotTable)
Dim pf As PivotField
Dim strPg As String
Dim strAll As String
On Error GoTo exit_Handler
Application.EnableEvents = False
Application.ScreenUpdating = False
If ActiveSheet.Name = Me.Name Then
For Each pf In Target.PageFields
If Target.PivotCache.OLAP Then
strPg = Right(pf.CurrentPageName, 5)
strAll = "[All]"
Else
strPg = pf.CurrentPage
strAll = "(All)"
End If
If strPg = strAll Then
Application.Undo
MsgBox "Please select a single item."
End If
Next pf
End If
exit_Handler:
Set pf = Nothing
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
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.

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

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:
NOTE: This will only change the first Report Filter, if there are multiple Report Filter fields.
If you want the scrolling to stop at the beginning and end of the items, instead of looping back to "(All)", delete the following line in each macro shown below, or type an apostrophe at the beginning of the line:
pf.CurrentPage = "(All)"
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
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.

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

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

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

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

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

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
Block (All) - Normal Pivot Table Only -- Download the Block All (Normal Pivot) sample file, which contains the pivot table and the code for Normal pivot tables only. The zipped file is in xlsm format, and contains the macro to block selection of (All) in the report filters, for a normal pivot table (not in the Data Model). To test the code, enable macros when you open the file.
Block (All) - OLAP or Normal Pivot Tables -- Download the Block All (Normal or OLAP) sample file, which contains both a Normal pivot table, and and OLAP-based (Data Model) pivot table. The zipped file is in xlsm format, and contains the macro to block selection of (All) in the report filters, for either type of pivot table. To test the code, enable macros when you open the file.
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.
Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.
Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!
Last updated: May 25, 2025 3:22 PM