Automatically Trigger AutoFilter Macro

[sweeneyd]

I have a worksheet (Database) that gets sorted based upon a couple of user entries on another worksheet (Budget). Some calculations are done on the filered results which are passed back to the user entry sheet. The code I'm using is:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

ActiveSheet.AutoFilterMode = False
Range("A4:R1000").autofilter
Range("A4:R1000").autofilter field:=4, Criteria1:=Sheets("Budget").Range("D8").Text
Range("A4:R1000").autofilter field:=5, Criteria1:=Sheets("Budget").Range("D10").Text

End Sub

Which works great, except that I need to open the Database worksheet and make a selection to trigger the filter. How can I trigger so that the autofilter happens and the Budget sheet is updated without going to the database sheet?

Thanks.

 

[Jerry Sullivan]

Welcome to the Board,

You can try the code below. Paste it into the Worksheet Code module of the Budget Sheet and delete your existing event code from the Database Sheet.

This code will only update the autofilters when cells D8 or D10 of your Budget sheet are changed - so it runs only when you need it instead of on every change of Selection.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D8,D10")) Is Nothing Then Exit Sub
    Dim str1 As String, str2 As String
    str1 = Range("D8").Value
    str2 = Range("D10").Value
   
    Application.ScreenUpdating = False
    With Sheets("Database")
        .AutoFilterMode = False
        .Range("A4:R1000").AutoFilter field:=4, Criteria1:=str1
        .Range("A4:R1000").AutoFilter field:=5, Criteria1:=str2
    End With
End Sub

 

[sweeneyd]

Perfect! Exactly what I needed. Thank you so much.