Reputation: 37
I have a macro (ApplyFilter) that filters through many worksheets based on a date I enter into cell B1 on another worksheet (Grand Totals). That macro is:
Sub ApplyFilter() 'Filters all worksheets except worksheet1 for date entered into _
'Grand Totals!B1
Dim WS_Count As Integer
Dim I As Integer
Dim FilterRange As Variant
FilterRange = Range("'Grand Totals'!B1")
' Set WS_Count equal to the number of worksheets in the active
' workbook.
WS_Count = ActiveWorkbook.Worksheets.Count
' Begin the loop.
For I = 2 To WS_Count
Sheets(I).Select
ActiveSheet.AutoFilterMode = False 'Remove any existing filters
Worksheets(I).Range("A2").AutoFilter Field:=1, Criteria1:=Range("'Grand Totals'!B1").Text
Next I
Sheet1.Activate
End Sub
When I execute this macro manually, it executes and filters as it should. However, when I call this macro from another sub:
Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("B1")) Is Nothing Then _
Call ApplyFilter
End Sub
I get a "Macros" window that offers a list of macros available. I can select the "ApplyFilter" macro and click Run and the Macro executes and filters the worksheets as I desire.
I found many references to automatically executing a macro from within a sub, but none refer to the "Macros" window, from which I now must select the macro to run. Instead, when I enter a date in cell B1 of the "Grand Totals" worksheet and hit enter, sub worksheet_change(ByVal Target As Range) should automatically call "ApplyFilter" and apply the date filter to the many worksheets.
I have created a button and used Button_Click to call "ApplyFilter", and all is well. But, it seems more intuitive to enter the date and then press Enter to execute the macro. I could put up with the Button_Click method, but I'm trying to learn VBA firstly, and I'm just stubborn enough to want to learn how to make it work, and I do not want settle just for what will work.
Upvotes: 2
Views: 4557
Reputation: 55712
Sheet Code must be in Grand Totals
sheet
Grand Totals
sheet tabView Code
Grand Totals Sheet Code
Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("B1")) Is Nothing Then Call ApplyFilter
End Sub
more efficient filter code
Sub ApplyFilter()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
If ws.Name <> "Grand Totals" Then
ws.AutoFilterMode = False
ws.Range("A2").AutoFilter Field:=1, Criteria1:=Range("'Grand Totals'!B1").Text
End If
Next
End Sub
Upvotes: 2