Pivot table - filter by VBA

[mrmaso]

Hello,
could somebody help me with this issue:
- I have pivot table where I would like to apply automatic filter by macro. All needed values which should be used are in cells B5:B55. Is it possible to write macro which will use these cells to set up filter?

Thank you.

 

[DanteAmor]

Change the red data by your information

Sub Set_Sales_History_Time_Period()
    Dim pvt As PivotTable, pi As PivotItem, n As Long, Rng As Range, f As Range
    
    Set Rng = Range("[COLOR=#ff0000]B5:B55[/COLOR]")
    n = 0
    Application.ScreenUpdating = False
    Set pvt = ActiveSheet.PivotTables("[COLOR=#ff0000]Table1[/COLOR]")  [COLOR=#006400]'Table name[/COLOR]
    With pvt.PivotFields("[COLOR=#ff0000]Name[/COLOR]")   [COLOR=#006400]'Name of the field to filter[/COLOR]
        .ClearAllFilters
        For Each pi In .PivotItems
          Set f = Rng.Find(pi, , xlValues, xlWhole)
          If f Is Nothing Then
            n = n + 1
            If n < .PivotItems.Count Then
              pi.Visible = False
            Else
              MsgBox "No match"
              .ClearAllFilters
            End If
          End If
        Next
    End With
End Sub

 

[mrmaso]

Working fantastic. Thank you.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.