Use macros to quickly change the report filters in an Excel pivot table, or block filter changes
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.
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.
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.
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.
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
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.
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.
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
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
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.
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
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
Last updated: February 19, 2017 2:44 PM