excel 2010 vba pivot table report filter update

Question

I have 16 pivot tables on the same sheet, "Analytics Admin", I recorded and cleaned up the macro below (the code right before updates the info the pivot tables source). It's basically the same code 16 times, the only thing that changes is the pivot table name ( MSP, MSP30, FSP, FSP30, etc). Is it possible to change the report filter for all the pivot tables at once?

Worksheets("Analytics Admin").Activate

'refreshes all data, clears all filters then filters 0 and blanks out of all custom range pareto pivot tables

ActiveSheet.PivotTables("MSP").PivotCache.Refresh
    ActiveSheet.PivotTables("MSP").PivotFields("count").ClearAllFilters
    ActiveSheet.PivotTables("MSP").PivotFields("count").ShowAllItems = True

    With ActiveSheet.PivotTables("MSP").PivotFields("count")
        .PivotItems("0").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    ActiveSheet.PivotTables("MSP").PivotCache.Refresh

ActiveSheet.PivotTables("FSP").PivotCache.Refresh
    ActiveSheet.PivotTables("FSP").PivotFields("count").ClearAllFilters
    ActiveSheet.PivotTables("FSP").PivotFields("count").ShowAllItems = True

    With ActiveSheet.PivotTables("FSP").PivotFields("count")
        .PivotItems("0").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    ActiveSheet.PivotTables("FSP").PivotCache.Refresh

have you considered looping through all pivot tables on the sheet? — teylyn
somehow I've managed to never use loops before could you elaborate a little? — Alberto Brown

teylyn teylyn · Accepted Answer · 2016-02-03T03:14:58

Something along the lines of (not tested, just typed into here)

    dim ws as Worksheet
    set ws = ThisWorkbook.Worksheets("Analytics Admin")
    dim pt as PivotTable
        For Each pt In ws.PivotTables
'            Debug.Print Pivot.Name
            pt.PivotFields("count").ClearAllFilters
            pt.PivotFields("count").ShowAllItems = True

            With pt.PivotFields("count")
              .PivotItems("0").Visible = False
              .PivotItems("(blank)").Visible = False
            End With
            pt.PivotCache.Refresh
            pt.refreshTable
            pt.Update
        Next

excel 2010 vba pivot table report filter update

2 Answers