Loop through all Pivot tables in a worksheet and filter visability VBA

[garbour]

I have what I thought was to be a straightforward task to accomplish, but two days later I cannot figure out how to do this.

I've posted the simplest code of what I want to accomplish (for a single PivotTable). This code will turn off visibility of Name1-6 in Pivottable1. What I need the code to accomplish is to apply this filter to every PivotTable in my sheet (not workbook, only the activesheet), approximately 50 of them. Also, in there are instances where "Name2" or "Name4", etc. does not occur in a PivotTable. I would like the code to move on to the next name rather than throw an error.

Excel 2013

Sub filterPivot()

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Memo")
        .PivotItems("Name1").Visible = False
        .PivotItems("Name2").Visible = False
        .PivotItems("Name3").Visible = False
        .PivotItems("Name4").Visible = False
        .PivotItems("Name5").Visible = False
        .PivotItems("Name6").Visible = False
    End With
End Sub

Thanks to anyone who can help me out!

[Paul_Hossler]

I don't think I've ever heard of 50 pivot tables on a single sheet, but try this

Option Explicit

Sub filterPivot()
    Dim pt As PivotTable
    
    On Error Resume Next
    For Each pt In ActiveSheet.PivotTables
        With pt.PivotFields("Memo")
            .PivotItems("Name1").Visible = False
            .PivotItems("Name2").Visible = False
            .PivotItems("Name3").Visible = False
            .PivotItems("Name4").Visible = False
            .PivotItems("Name5").Visible = False
            .PivotItems("Name6").Visible = False
        End With
    Next
End Sub

[garbour]

Hi Paul,

Thanks for your help.

I tried your code and it only worked on the first PT in the worksheet, which is PivotTable49.

Regards,

greg

[Paul_Hossler]

Hmmm, should have worked (famous last words)

If you want, try this version

I added two Msgboxs and moved the On Error to which handles no pivot field named Memo and no PivotItem = Namex

Option Explicit

Sub filterPivot()

    Dim pt As PivotTable
     
    MsgBox ActiveSheet.PivotTables.Count
    
    For Each pt In ActiveSheet.PivotTables
        
        MsgBox pt.Name
        
        On Error Resume Next
        With pt.PivotFields("Memo")
            .PivotItems("Name1").Visible = False
            .PivotItems("Name2").Visible = False
            .PivotItems("Name3").Visible = False
            .PivotItems("Name4").Visible = False
            .PivotItems("Name5").Visible = False
            .PivotItems("Name6").Visible = False
        End With
        On Error GoTo 0
    Next

End Sub

[garbour]

Paul,

I figured it out... needed to add pt.update and pt.refresh.

Thanks for your help!

Sub filterPivot()

    Dim pt As PivotTable

    For Each pt In ActiveSheet.PivotTables
    
        pt.RefreshTable
        pt.Update


        On Error Resume Next
        With pt.PivotFields("Memo")
            .PivotItems("Name1").Visible = False
            .PivotItems("Name2").Visible = False
            .PivotItems("Name3").Visible = False
            .PivotItems("Name4").Visible = False
            .PivotItems("Name5").Visible = False
            .PivotItems("Name6").Visible = False

        End With
        On Error GoTo 0
        
    Next

End Sub