Pivot table filter based on value using VBA code

[Raj.end300]

Hi All,

The given below code filter the Pivot table based on cell value. I created this in module. But I want to implement this in specific worksheet "Market_MU Summary",

as I run this in "private sub procedure" with "change" event, the code just keep refreshing the pivots and doesn't stop even it hangs the excel.

Please any help on this.

Option Explicit

Sub Worksheet_Change1111()
    Dim a As String
    Dim pt As PivotTable
    Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Market_MU Summary")
    Set pt = ws.PivotTables("PivotTable3")
' Activate the worksheet containing the PivotTable
    'ThisWorkbook.Worksheets("Market_MU Summary").Activate
' Get the value from cell C3
    a = Worksheets("Market_MU Summary").Cells(3, 33).Value
' Loop through all PivotTables in the active sheet
    For Each pt In ActiveSheet.PivotTables
        With pt.PivotFields("Uniq Count")
            ' Check if "Uniq Count" is a valid field in the PivotTable
            If .Orientation = xlPageField Then
                .ClearAllFilters
                .CurrentPage = a
            End If
        End With
    Next pt
End Sub

[arnelgp]

whenever you make changes to your pivot, the Change event fires, that is the reason you are in an endless loop.
you must first make sure to disable all events before making changes, then re-instate all events.

[Raj.end300]

Thank you sir for your reply, I am actually new to VBA and learning it, it is solved. Many many thanks again sir.

[Aussiebear]

Sorry raj.end300 but how is it solved? arnelgp raised a legitimate query to your post. Why was your solution?

[Raj.end300]

I just needed hint. Arnelgp provided me good. Here is the updated code :

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Dim a As String
    Dim pt As PivotTable
    Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Market_MU Summary")
    Set pt = ws.PivotTables("PivotTable3")
' Activate the worksheet containing the PivotTable
    'ThisWorkbook.Worksheets("Market_MU Summary").Activate
' Get the value from cell C3
    a = Worksheets("Market_MU Summary").Cells(3, 33).Value
' Loop through all PivotTables in the active sheet
    For Each pt In ActiveSheet.PivotTables
        With pt.PivotFields("Uniq Count")
            ' Check if "Uniq Count" is a valid field in the PivotTable
            If .Orientation = xlPageField Then
                .ClearAllFilters
                .CurrentPage = a
            End If
        End With
    Next pt
Application.EnableEvents = True
End Sub

[Aussiebear]

Thank you Raj.end300 for posting your solution