none
"Unable to set the visible property of the pivotitem class" RRS feed

  • Question

  • Hi I'm trying to filter some pivot tables using VBA, but in this filter I need to select only when some word appear, for exemple:

    I need to filter "Finance" and my options on table are :

    • Finance; Adm
    • Adm
    • Adm; Law
    • Law; Finance

    So I filter only the first and last option. For that I'm using this code, but for some reason started to show the "Unable to set the visible property of the pivotitem class" error mensage:

    Sub DocumentosPadronizados()
    
        Dim PvtTbl      As PivotTable
        Dim PvtItm      As PivotItem
        Dim f           As String
    
        f = Range("E1")
        
        ' set the pivot table'
        
        Set PvtTbl = Sheets("Base_Dash").PivotTables("Tabela dinâmica4")
        
    
        If Range("E1") = " Todas áreas" Then
            With PvtTbl.PivotFields("Pontas")
            .ClearAllFilters
            End With
        
        Else
            
        With PvtTbl.PivotFields("Pontas")
            .ClearAllFilters
    
            For Each PvtItm In .PivotItems
                If PvtItm.Name Like "*" & f & "*" Then
                    PvtItm.Visible = True
                Else
                    PvtItm.Visible = False
                End If
            Next PvtItm
        End With
        
        End If
    
    End Sub


    Thursday, April 26, 2018 1:38 PM

All replies

  • Hello Davi Mathielo,

    I tried to build a simply pivot table for testing but did not reproduce your issue.

    I would suggest you try to refer to below thread to set PivotCache.MissingItemsLimit and refresh the pivot table before looping. If they both could not work for, please share a simply workbook with the pivot table so we could try to use it to reproduce your issue.

    Unable to set the Visible property of the PivotItem class (VBA)

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 27, 2018 2:17 AM