troubleshooting Question

Trigger change event on Page Filter

Avatar of deskchains
deskchainsFlag for United States of America asked on
Microsoft Excel
2 Comments1 Solution236 ViewsLast Modified:
I use the following to update several pivot table page filters based on the filter selection of one master filter:

*********************************
Private Sub Worksheet_Change(ByVal Target As Range)

    On Error Resume Next
     Application.EnableEvents = False
    If Target.PivotCell.Parent = "Master" Then
        ActiveSheet.PivotTables("Slave1").PageFields("Stand").CurrentPage = ActiveSheet.PivotTables("Master").PageFields("Stand").CurrentPage.Name
        ActiveSheet.PivotTables("Slave2").PageFields("Stand").CurrentPage = ActiveSheet.PivotTables("Master").PageFields("Stand").CurrentPage.Name
        ActiveSheet.PivotTables("Slave3").PageFields("Stand").CurrentPage = ActiveSheet.PivotTables("Master").PageFields("Stand").CurrentPage.Name
        ActiveSheet.PivotTables("Slave4").PageFields("Stand").CurrentPage = ActiveSheet.PivotTables("Master").PageFields("Stand").CurrentPage.Name
        ActiveSheet.PivotTables("Slave6").PageFields("Stand").CurrentPage = ActiveSheet.PivotTables("Master").PageFields("Stand").CurrentPage.Name
    End If
     Application.EnableEvents = True

End Sub

Works fine, but.  Any time you change ANYTHING on/in the workbook the whole screen flashes/blinks because the routine is being triggered to run.  Fairly annoying.

I would like to change the code so that it will only run when the page filter of the "Master" pivot table is changed.  I have tried unsuccessfully to tie it to the cell that the page filter of master is in  = cell B6
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 2 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros