Add multiple filter items to a pivot table from a named range

megan947us

New Member
I want to add multiple filter items to a pivot table, and these filter items are from a named range in the current worksheet

I'm getting the error "Unable to set the Visible property of the PivotItem class". What am I doing wrong?

Code:
Sub PivotUpdate()

    Dim PI As PivotItem
    Dim vRNG
           
    vRNG = Application.Transpose(Worksheets("Agreements_Materials").Range("Agreement_Filter"))
        


    ActiveSheet.PivotTables("PivotTable4").PivotFields("[AgreementMaterials].[AgreementNumber].[AgreementNumber]").ClearAllFilters
    
    For Each PI In ActiveSheet.PivotTables("PivotTable4").PivotFields("[AgreementMaterials].[AgreementNumber].[AgreementNumber]").PivotItems
        If Len(Join(Filter(vRNG, PI, True, vbBinaryCompare))) = 0 Then
            PI.Visible = False
        End If
    Next PI



  End Sub
 

DanteAmor

Well-known Member
It means that none of the items meets the condition, so try to hide all, but you have to leave one item visible. If you hide all you will have the error message.
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">It means that none of the items meets the condition, so try to hide all, but you have to leave one item visible. If you hide all you will have the error message.</body>
 

Some videos you may like

This Week's Hot Topics

Top