unable to get the visible property of the pivotitem class

I have two sets of code. For some reason, in the first one I get an error, and in the second one I don't.

1:

Dim pi As PivotItem
Dim pf As PivotField

Set pf = Sheets("Afname per school").PivotTables("Draaitabel3").PivotFields("school")

For Each pi In pf.PivotItems
    If pi = "(leeg)" Then
        pi.Visible = False
    Else
        pi.Visible = True 'ERROR HERE
    End If
Next pi

2:

Dim pi As PivotItem
Dim pf As PivotField

Set pf = Sheets("Afname per school").PivotTables("Draaitabel3").PivotFields("naam locatie")

For Each pi In pf.PivotItems
    If InStr(pi, "BSO") Then
        pi.Visible = True
    Else
        pi.Visible = False
    End If
Next pi

I get the error: "unable to get the visible property of the pivotitem class"

I read I should solve this the following:

This is due to the Pivot table using the cached pivot items instead of the current one. Make sure the table does not retain any old items. To do so, right click on your pivot table, click on Data tab and set "Number of itesm to retain per field" to "None". The code to do so in VBA is:

Dim pt As PivotTable

pt.PivotCache.MissingItemsLimit = xlMissingItemsNone

I tried to add this line of code in two ways:

Dim pi As PivotItem
Dim pf As PivotField

Set pf = Sheets("Afname per school").PivotTables("Draaitabel3").PivotFields("school")

pt.PivotCache.MissingItemsLimit = xlMissingItemsNone  '1st TRY

For Each pi In pf.PivotItems
    pt.PivotCache.MissingItemsLimit = xlMissingItemsNone   '2nd TRY
    If pi = "(leeg)" Then
        pi.Visible = False
    Else
        pi.Visible = True
    End If
Next pi

This doesn't seem to solve my problem.

1. Do you have multiples fields in your rows/columns fields?

Because the problem might be coming from this.

All the PivotItems in PivotField are not always displayed/displayable because they are in the second level, depending on the first level. To avoid code breaking because of the error, you'll have to use an Error Handler.

Only the PivotItems that are found with the corresponding PivotItem from the first level are displayable (IE you can't displayed a case that didn't happen in your data).

For instance you can't display the PivotItem "Car" at 2nd level

when the 1st level PivotItem is "Flying mode of transportation".

---

2. Refresh PivotCache

That being said, you could simply refresh the pivot cache (check that you have defined Pt as your PivotTable) right after using the setting MissingItemsLimit to be sure that you have the most recent data :

Set Pt = Sheets("Afname per school").PivotTables("Draaitabel3")
Set pf = Pt.PivotFields("school")
Pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
Pt.PivotCache.Refresh

---

3. Code logic

Looking at your code afterwards, I'm a bit perplex, because what you are doing is hiding a specific PivotItem found by his name, but you also try to display every other PivotItem!

And I think that is the main problem here, I would suggest a routine with few arguments and error handling, something like this :

Sub Hide_PivotItem(PivotTable_Object As PivotTable, _
                    PivotField_Name As String, _
                    ByVal PivotItem_Name As String, _
                    ByVal UnHide_All As Boolean)

    Dim Pt As PivotTable, _
        Pi As PivotItem, _
        Pf As PivotField
    
    Set Pt = PivotTable_Object
    Set Pf = Pt.PivotFields(PivotField_Name)
    
    Pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
    Pt.PivotCache.Refresh
    
    If UnHide_All Then
        On Error Resume Next
        For Each Pi In Pf.PivotItems
            Pi.Visible = True
        Next Pi
        On Error GoTo 0
    Else
        'Don't unhide other items
    End If

    For Each Pi In Pf.PivotItems
        If Pi.Name <> PivotItem_Name Then
        Else
            Pi.Visible = False
        End If
    Next Pi

End Sub