Hide all pivotitems except 2 w-VBA

[jakeman]

Hello everyone - I grabbed some code from another site on how to set all pivotitems = false except two items I want to display. It seems like it should be straightforward but I keep getting the following error:

 Run-time error '438': Object doesn't support this property or method

Here is the VBA code I am using:

Sub hidepivotitems()
'
'
    Dim oPI As PivotItem
    
    For Each oPI In ActiveSheet.PivotTables("PivotTable2").PivotFields("User")
        Select Case oPI.Name
            Case "Smith, Joe", "Black, Joe"
            Case Else
                oPI.Visible = False
        End Select
    Next oPI
    
End Sub

I am using VBA 7.0 on Excel 2010. Also, the code is failing on the "For Each oPI" line.

Thanks.

 

[Andrew Poulsom]

Try:

For Each oPI In ActiveSheet.PivotTables("PivotTable2").PivotFields("User").PivotItems

 

[jakeman]

Try:

For Each oPI In ActiveSheet.PivotTables("PivotTable2").PivotFields("User").PivotItems

worked perfectly...thx, Andrew!

 

[dsmith616]

trying to revive this old thread as i have a similar issue; the above code works but i am also trying to add a line that resets the pivot table to show all and then sorts based on case select. so far i haven't been able to get it working.

Sub DigitSplit8()
'
' Macro14 Macro
'


    Dim oPI As PivotItem




    For Each oPI In ActiveWorkbook.Worksheets("60Day").PivotTables("Test").PivotFields("Digit").PivotItems
        oPI.Visible = True
        Select Case oPI.Value
            Case "80", "81", "82", "83", "84", "35", "86", "87", "88", "89"
            Case Else
                oPI.Visible = False
        End Select
    Next oPI


End Sub
'
Sub DigitSplit9()
'
' Macro14 Macro
'


    Dim oPI As PivotItem


    For Each oPI In ActiveWorkbook.Worksheets("60Day").PivotTables("Test").PivotFields("Digit").PivotItems
       oPI.Visible = True
        Select Case oPI.Value
            Case "90", "91", "92", "93", "94", "95", "96", "97", "98", "99"
            Case Else
                oPI.Visible = False
        End Select
    Next oPI


End Sub

 

[Tanner_2004]

This also worked for me Andrew. Thank you.
Is there a way to clear all filters, for the items before running this?
Thank you.

Try:

For Each oPI In ActiveSheet.PivotTables("PivotTable2").PivotFields("User").PivotItems