excel 2010 vba loop pivot tables

Question

I have 16 pivot tables that I need to update when the user presses a button. The code that needs to run is as follows:

ActiveSheet.PivotTables("PivotTable10").PivotFields("count").ClearAllFilters
        ActiveSheet.PivotTables("PivotTable10").PivotFields("scrap code").ClearAllFilters
               ActiveSheet.PivotTables("PivotTable10").PivotFields("count").ShowAllItems = True

        With ActiveSheet.PivotTables("PivotTable10").PivotFields("count")
            .PivotItems("0").Visible = False
        End With

The names of the pivot tables are: MSP,MSP30,FSP,FSP30,MRP,MRP30,FRP,FRP30,MPP,MPP30,FPP,FPP30,MCP,MCP30,FCP,FCP30

I'd like to replace PivotTable10 with a variable that loops thru that list of pivot tables. Right now my code is 16 blocks of the code above. I know little about loops and I haven't found a good example of this type of loop in my google searches.

EDIT: finally code that worked, both answers below worked perfectly, this code just worked slightly faster

Sub IteratePivots()

Application.ScreenUpdating = False

On Error Resume Next

Worksheets("Analytics Admin").Activate

Dim pvtTables As PivotTables
Dim pvtTable As PivotTable
Set pvtTables = Application.ActiveSheet.PivotTables
For Each pvtTable In pvtTables
    pvtTable.PivotFields("count").ClearAllFilters
    pvtTable.PivotFields("scrap code").ClearAllFilters
    pvtTable.PivotFields("count").ShowAllItems = True

    With pvtTable.PivotFields("count")
        .PivotItems("0").Visible = False
    End With
Next

Application.ScreenUpdating = False
End Sub

Only Pivottable names alone are going to change in you code am i right? — Siva, Feb 04 '16 at 15:22
that is correct I'd run those exact 4 lines each time ( I may have to add an error correction for when the only value is 0) . — Alberto Brown, Feb 04 '16 at 15:29

score 3 · Accepted Answer · answered Feb 04 '16 at 15:41

Try this code. This code will iterates through all the pivotable in your sheet.

Sub IteratePivots()
Dim pvtTables As PivotTables
Dim pvtTable As PivotTable
Set pvtTables = Application.ActiveSheet.PivotTables
For Each pvtTable In pvtTables
    pvtTable.PivotFields("count").ClearAllFilters
    pvtTable.PivotFields("scrap code").ClearAllFilters
    pvtTable.PivotFields("count").ShowAllItems = True

    With pvtTable.PivotFields("count")
        .PivotItems("0").Visible = False
    End With
Next
End Sub

thanks this works slightly faster then @hambones code, by like 6 seconds. — Alberto Brown, Feb 04 '16 at 16:04

score 2 · Answer 2 · edited May 23 '17 at 12:30

It's a little bit of a hack, but something like this might to the trick:

  Dim pt As PivotTable
  Dim ws As Worksheet
  Dim names As Variant
  Dim found As Boolean

  Set ws = ActiveWorkbook.ActiveSheet
  names = Split("MSP,MSP30,FSP,FSP30,MRP,MRP30,FRP,FRP30,MPP,MPP30,FPP,FPP30,MCP,MCP30,FCP,FCP30", ",")

  For Each pt In ws.PivotTables
    found = False

    For i = 1 To UBound(names)
      If pt.Name = names(i) Then
        found = True
      End If
    Next i

    If found Then
      pt.PivotFields("count").ClearAllFilters
      pt.PivotFields("scrap code").ClearAllFilters
      pt.PivotFields("count").ShowAllItems = True

      pt.PivotFields("count").PivotItems("0").Visible = False
    End If

  Next pt

You may notice my clumsy check to see if the name is in the array... This is a much better solution for that piece:

https://stackoverflow.com/a/10952705/190829

this is damn close to what I was trying to do, the only issues is that some of the pivot tables only value is 0 , and I get an error `unable to set the visible property of the PivotItem class`. — Alberto Brown, Feb 04 '16 at 15:38
@AlbertoBrown, In that case Check, if the value<>0 before you proceed. — Siva, Feb 04 '16 at 15:45

excel 2010 vba loop pivot tables

2 Answers2