Filter pivot from a range

[cba321]

Found a code from Andrew in http://www.mrexcel.com/forum/showthread.php?t=475636 to filter pivot from a range of data. But I encounter a problem where the loop in pivotItems seems like it can only travel upwards and have error if the new selected item is downward.

I'm populating the range from a listbox with multiple selection function.

Items sorting in pivot:
a
b
c
d
e

For eg:
1) 1st time select "c" from listbox and populate to the range - code is working
2) re-select "b" from listbox - code is working
3) if re-select again"c" or "d" or "e" - code is not working with error msg "Unable to set the visible property of PivotItem class"

Any help? Thanks

Andrew's code:
Sub PT()
'
'PT Macro
'
Dim PT As PivotTable
Dim PI As PivotItem
Set PT = Sheets("Sheet3").PivotTables("PivotTable2")
PT.PivotFields("Server").CurrentPage = "(All)"
For Each PI in PT.PivotFields("Server").PivotItems
PI.Visible = WorksheetFunction.CountIf(Sheets("Sheet2").Range("A1:A10"), PI.Name) > 0
Next PI
Set PT = Nothing
End Sub

 

[DonkeyOte]

First, please outline in which XL version was the Pivot created ?

There are significant version differences when it comes to Pivots - esp. handling Multi Page Items (if that's a likely requirement) and Clearing Filters

The fundamental issue here is you must always have 1+ items visible, when hiding "c" you will get a Debug as you need a visible item
(you've not got to "d" yet and your (All) line won't work as you expect)

If you're using XL2007+ things are much simplified.

Be sure to check out Debra Dalgleish's website Contextures for info. on all things Pivot.

 

[cba321]

I'm using Excel 2007.

So how do i tweak the code below to always have 1+ item visible. It is working perfectly if select item above the first selection, but not if select item below the first selection. Thanks

For Each PI in PT.PivotFields("Server").PivotItems
PI.Visible = WorksheetFunction.CountIf(Sheets("Sheet2").Range("A1:A10"), PI.Name) > 0
Next PI

 

[DonkeyOte]

If you're running XL2007 then assuming you've no other filters (to persist) I'd be inclined to utilise the ClearAllFilters method:

Sub PT()
    '
    'PT Macro
    '
    Dim PT As PivotTable
    Dim PI As PivotItem
    Set PT = Sheets("Sheet3").PivotTables("PivotTable2")
    With PT
        .ManualUpdate = True
        .ClearAllFilters
        For Each PI in .PivotFields("Server").PivotItems
            On Error Resume Next
            PI.Visible = WorksheetFunction.CountIf(Sheets("Sheet2").Range("A1:A10"), PI.Name) > 0
            On Error GoTo 0
        Next PI
        .ManualUpdate = False
    End With
    Set PT = Nothing
End Sub

The use of the On Error Resume Next is a little "catch all" though.