CODEWITHSUNDEEP
vbaexcelpivot-table
user1507455
user1507455

Reputation: 1133

Looping through report filters to change visibility doesn't work

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.)

Upvotes: 4

Views: 17566

Answers (2)

user1507455
user1507455

Reputation: 1133

In a pivottable filter, you must have at least one item selected at all times. Even if you intend to select one later in the code.

With Pt.PivotFields("COUNTRYSCENARIO")

 ' Sets all filters to true, resetting it.
 .ClearAllFilters

 ' This is necessary if you want to select any options
 ' other than "All Pivot Items = Visible" and 
 ' "OnlyOneSpecificPivotItem = Visible"
 .EnableMultiplePageItems = True

 If .PivotItems.Count > 0 Then

     ' goofy but necessary
     Set firstPi = .PivotItems(1)

         For Each Pi In .PivotItems                                

             ' Make sure that that first pivot item is visible.
             ' It gets mad if it's already visible and you
             ' set it to visible with firstPi.Visible = True
             ' ...pretty silly

             If firstPi.Visible = False Then firstPi.Visible = True

             ' Don't loop through firstPi
             If Pi.Value <> firstPi.Value Then

                 If Pi.Value = opt1 Or Pi.Value = opt2 Or Pi.Value = opt3 Then
                     Pi.Visible = True

                 ElseIf Pi.Visible = True Then

                     Pi.Visible = False

                 End If

             End If

          Next Pi

          ' Finally perform the check on the first pivot item   
          If firstPi = opt1 Or firstPi = opt2 Or firstPi = opt3 Then
              firstPi.Visible = True
          Else
              firstPi.Visible = False
          End If
       End If
End With

Notice that if you try to select nothing, e.g. opt 1 = "" and opt2 = "" and opt3 = "", you will have that same error: you must have at least one pivot item selected.

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149325

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

Upvotes: 7

Related Questions