Saturday, 19 March 2016

How to Apply Pivot Filter from a Range of Values in Excel

How to Pass a Range of Values to Pivot Filter in Excel
Sub Apply_Pivot_Filters_from_Range()
Dim Pvt As PivotTable
Dim Pvt_Item As PivotItem
Dim Input_Rng As Range
Dim Rng As Range
Dim Pvt_Sht As Worksheet

Dim blnTrueFalse As Boolean
blnTrueFalse = False

Set Pvt_Sht = ThisWorkbook.Sheets("Pivots_Tab")

Set Pvt = Pvt_Sht.PivotTables("PivotTable1")

'Finding the Input Range with Values to Pass into the Pivot Filter
Set Input_Rng = Pvt_Sht.Range("A2").Resize(Range("A" & Rows.Count).End(xlUp).Row - 1, 1)
        Pvt.PivotFields("Sales_Period").CurrentPage = "(All)"
        Pvt.PivotFields("Sales_Period").EnableMultiplePageItems = True

'Applying/Passing each Range value to Pivot Filter
For Each Rng In Input_Rng

 For Each Pvt_Item In Pvt.PivotFields("Sales_Period").PivotItems
'All PivotItems which Matches to the Rng value will be Selected for Filter. 
     If Rng = Pvt_Item Then
         Pvt_Item.Visible = True
'All PivotItems which does not Matches to the Rng value will be De-selected from Filter.
     ElseIf Rng <> Pvt_Item And blnTrueFalse = False Then
        Pvt_Item.Visible = False
     End If

 Next Pvt_Item
 'Setting the Boolean value to 'True', so that in the next loop the Pivot will apply Filter only to the Matching values and does not cares about the Non-Matching UnFIltered values.
 blnTrueFalse = True

Next Rng 

MsgBox "All Input Range Values Applied to Pivot Filter", vbOKCancel, "Apply Filter"
End Sub
Example :
Suppose we have a Pivot Table and the Range of Values to Pass into Pivot Filter as follows :
 Pivot Table :
Range of Values to Pass into Pivot Filter "Sales_Period"

Output :

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your open Feedback only related to this Blog Posts. Please note that I cannot respond to the Anonymous Comments.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts