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 :
Output :
--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------
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.