Microsoft Excel - filter pivot based on a range

Asked By Cherifa Hima on 19-Feb-13 10:39 PM
I asked this question before and I found the answer. Here it is for anyone who needs it: include and or exclude

Sub INCLUDEONLY()
Dim PT As PivotTable
Dim PI As PivotItem
Dim Hotel As Range
' FILTER PIVOT BASED ON THE LIST


Set PT = Sheets("Query").PivotTables("PivotTable1")
For Each PI In ActiveSheet.PivotTables("PivotTable1").PivotFields("ID").PivotItems
    PI.Visible = True
Next PI


'PT.PivotFields("ID").CurrentPage = "(All)"
'On Error Resume Next
For Each PI In PT.PivotFields("ID").PivotItems
    PI.Visible = WorksheetFunction.CountIf(Sheets("Hotel List").Range("HOTEL"), PI.Name) > 0
Next PI
Set PT = Nothing
End Sub




Sub EXCLUDE()
Dim PT As PivotTable
Dim PI As PivotItem
Dim Hotel As Range
' FILTER PIVOT BASED ON THE LIST


Set PT = Sheets("Query").PivotTables("PivotTable1")
For Each PI In ActiveSheet.PivotTables("PivotTable1").PivotFields("ID").PivotItems
    PI.Visible = True
Next PI


'PT.PivotFields("ID").CurrentPage = "(All)"
'On Error Resume Next
For Each PI In PT.PivotFields("ID").PivotItems
    PI.Visible = WorksheetFunction.CountIf(Sheets("Hotel List").Range("HOTEL"), PI.Name) = 0
Next PI
Set PT = Nothing


End Sub