VBA to EXclude pivotitems in named range

[Dahlia]

Hello Excel Experts,

I found several amazing macros from amazing excel experts to filter pivotitems, but it covers to INCLUDE pivotitems in namedrange or after minor change, I can get to EXCLUDE pivotitem but only 1 item or multiple items if I hardcode them all as fixed in VBA. However, what I really need is for it to be able to filter out by namedrange because the input may change from time to time because the input is holidays dates throughout the year.
Below are my findings so far. Perhaps someone expert can tweak some lines to meet my objective:-

Sub TestINclude()
Dim PI As PivotItem
With Worksheets("Sheet2").PivotTables("PivotKPI").PivotFields("AssignDt")
.ClearAllFilters
For Each PI In .PivotItems
PI.Visible = WorksheetFunction.CountIf(Range("cuti"), PI.Name) > 0
Next PI
End With
End Sub

Sub TestEXclude()
Dim PI As PivotItem
With Worksheets("Sheet2").PivotTables("PivotKPI").PivotFields("AssignDt")
.ClearAllFilters
'For Each PI In .PivotItems
' PI.Visible = WorksheetFunction.CountIf(Range("cuti"), PI.Name) > 0
'Next PI
.PivotItems(Range("cuti")).Visible = False
End With
End Sub

Appreciate your expertise.

Thank you in advance.

DZ

 

[AlanSidman]

crossposted: http://www.excelforum.com/excel-pro...-vba-to-exclude-pivotitems-in-namedrange.html