vba - Looping through report filters to change visibility doesn't work

Question

I'm trying to select one report filter, in this case Canada. That means the rest must be made invisible. This code works without issue:

Public Sub FilterPivotTable()

    With ActiveSheet.PivotTables("Epidemiology").PivotFields("COUNTRY")

        .PivotItems("Canada").Visible = True
        .PivotItems("USA").Visible = False
        .PivotItems("Germany").Visible = False
        .PivotItems("France").Visible = False

    End With

End Sub

However, I'm trying to prepare for when we add other countries to our "Epidemiology" pivot table, so I tried to have a for loop. This code doesn't work:

With ActiveSheet.PivotTables("Epidemiology").PivotFields("COUNTRY")

    .PivotItems("Canada").Visible = True

    For Each Pi In .PivotItems
        If Pi.Value = "CANADA" Then
            Pi.Visible = True
        Else
            Pi.Visible = False
        End If
    Next Pi

End With

It gives me an error on the Pi.Visible = False line. The error that I get is Run-time error '1004': Unable to set the Visible property of the PivotItem class

Why doesn't it work inside a for loop?!

Frustratingly, all the examples I find online use similar syntax. (Some use an index, but I tried that and got the same error.)

Answer 1

Is this what you are trying?

Sub Sample()
    Dim Pi As PivotItem

    With ActiveSheet.PivotTables("Epidemiology").PivotFields("COUNTRY")

        .PivotItems("Canada").Visible = True

        For Each Pi In .PivotItems
            If UCase(Pi.Value) = "CANADA" Then
                Pi.Visible = True
            Else
                Pi.Visible = False
            End If
        Next Pi
    End With
End Sub