In VBA I would like to set the visible PivotItems based on a list of values. For example I have a list of employees that work for a specific manager. I would like to loop through all the "Name" Items in the Pivot Table and set the ones in the "employee list" to visible and hide the others. Sample file attached.
My code attempt fails to clear the last item in the table so I always end up with an extra name that should not be there.
Any assistance is appreciated.
Dim PT As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Application.ScreenUpdating = False
Set PT = Sheets("EE Sample").PivotTables("PivotTable1")
Set pf = PT.PivotFields("Name")
PT.ManualUpdate = True
On Error Resume Next
For Each pi In pf.PivotItems
pi.Visible = False
i = 1
Do Until Range("EmpList").Offset(i, 0).Value = ""
EmpName = Range("EmpList").Offset(i, 0)
.PivotItems(EmpName).Visible = True
i = i + 1
PT.ManualUpdate = False
Application.ScreenUpdating = True