Using "For Each" for apply same filters for all pivot tables in the worksheet

1
votes

I guess this is an easy question but since I'm new in VBA I just can't figure it out. I have this code which goes to Pivot table1, select the DATA Pivot field and filter some values for this field.

Sub Multiple_Filtering()

Dim pf As PivotField

Set pf = ActiveSheet.PivotTables("Pivot table1").PivotFields("DATA")

'Enable filtering on multiple items
    pf.EnableMultiplePageItems = True  

'Must turn off items you do not want showing
    pf.PivotItems("201511").Visible = False
    pf.PivotItems("201512").Visible = False
    pf.PivotItems("201501").Visible = True
    pf.PivotItems("201502").Visible = True

End Sub

What I want to do is make a loop which does the same for all pivot tables in all worksheets in the workbook.

I found this piece of code which uses "For each" sentence to sets the data source for all pivot tables in the workbook, but I can't adapt it for my code.

Dim sht As Worksheet
Dim pvt As PivotTable

  For Each sht In ThisWorkbook.Worksheets
    For Each pvt In sht.PivotTables
          pvt.ChangePivotCache _
          ThisWorkbook.PivotCaches.Create( _
          SourceType:=xlDatabase, _
          SourceData:=SourceAddress)

        pvt.RefreshTable

    Next pvt
    Next sht

Any ideas?

2
excelvba

2 Answers

2
votes
Sub Multiple_Filtering()

Dim pf As PivotField
Dim sht As Worksheet
Dim pvt As PivotTable

    For Each sht In ThisWorkbook.Worksheets
        For Each pvt In sht.PivotTables
            Set pf = pvt.PivotFields("DATA")

            'Enable filtering on multiple items
            pf.EnableMultiplePageItems = True

            'Must turn off items you do not want showing
            pf.PivotItems("201511").Visible = False
            pf.PivotItems("201512").Visible = False
            pf.PivotItems("201501").Visible = True
            pf.PivotItems("201502").Visible = True
        Next pvt
    Next sht
End Sub
1
votes

I believe this should integrate the two pieces of code shown there

Sub Multiple_Filtering()

Dim sht As Worksheet
Dim pvt As PivotTable
Dim pf As PivotField

  For Each sht In ThisWorkbook.Worksheets
    For Each pvt In sht.PivotTables
          Set pf = sht.PivotTables(pvt).Pivotfields("DATA")
          'Enable filtering on multiple items
            pf.EnableMultiplePageItems = True  

          'Must turn off items you do not want showing
             pf.PivotItems("201511").Visible = False
             pf.PivotItems("201512").Visible = False
             pf.PivotItems("201501").Visible = True
             pf.PivotItems("201502").Visible = True

       'Admittedly not sure if this is necessary
        pvt.RefreshTable

    Next pvt
  Next sht


End Sub