|
|
Search
Contextures Sites
|
||
Pivot Table VBA - Report Filters
|
|
|
|||
|
||||
Contextures Inc., Copyright ©2013
All rights reserved.
Search Contextures Sites
Pivot Table VBA - Report Filters
Instead of manually changing the report filters in a pivot table, you can use Excel VBA code. These examples show how to programmatically change the filters, or block changes.
- Block Selection of (All) in Report Filter
- Change Filters in Same Pivot Table
- Create New Sheet for Each Item in Report Filter
For information on Report Filters, please visit:
Excel Pivot Table Report Filters
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 SubTo 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.
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 SubCreate 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
Contextures Inc., Copyright ©2013
All rights reserved.
Last updated: February 15, 2013
