Use 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 or if multiple items are 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.
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
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.
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.
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.
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) -- 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.
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:
and much more!
Last updated: January 3, 2018 1:23 PM