Saturday, March 19, 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")
Pvt_Sht.Activate

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

        Pvt.PivotFields("Sales_Period").ClearAllFilters
'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 genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog