VBA Code to Filter List by Variable Date

[elemexcel]

Hello

I have a worksheet where I have a list of accounts that will expire on different dates every month. I'm trying to create a VBA Macro to automate this and will filter a list then copy it to a separate sheet for ease.

What I have so far is (I'm new to VBA so recorded a Macro to get started)-

Sub ExpiringEDates_v1()
'
' ExpiringEDates_v1 Macro
'


'
    Sheets("Raw_Data").Select
    Selection.AutoFilter
    Sheets("Expiry_Dates").Select
    Range("D4").Select
    Selection.Copy
    Sheets("Raw_Data").Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveSheet.Range("$A$1:$X$1742").AutoFilter Field:=13, Criteria1:=">=" & Sheets("Expiry_Dates").Range("D4"), Operator:=xlAnd, Criteria2:="<=" & Sheets("Expiry_Dates").Range("D4")
    
    ActiveWindow.SmallScroll Down:=-42
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 1
    Columns("B:E").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Expiring_List").Select
    Range("A1").Select
    ActiveSheet.Paste
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("G20").Select
    Sheets("Raw_Data").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    Range("A1").Select
    Sheets("Expiring_List").Select
    Range("E32").Select
End Sub

This gives me a blank result, however when I change it and add in the next date manually i.e.

ActiveSheet.Range("$A$1:$X$1742").AutoFilter Field:=13, Criteria1:=
"enter date here"

That works for that specific date.

Can somebody help?

Thanks

 

[Osvaldo Palmeiro]

Hi. Try:

Sub ExpiringEDates_v2()
 Dim LR As Long
  With Sheets("Raw_Data")
   .AutoFilterMode = False
   LR = .Cells(Rows.Count, 1).End(3).Row
   .Range("A1:X" & LR).AutoFilter Field:=13, Criteria1:=">=" & CLng(Sheets("Expiry_Dates").Range("D4").Value), _
     Operator:=xlAnd, Criteria2:="<=" & CLng(Sheets("Expiry_Dates").Range("D4").Value)
   .Range("B1:E" & LR).Copy Sheets("Expiring_List").Range("A1")
   .AutoFilterMode = False
  End With
  Sheets("Expiring_List").Columns("A:D").AutoFit
End Sub

As a comment, the criteria in your original code is [column M date] >= [Expiry_Dates!D4] and [column M date] <= [Expiry_Dates!D4].

That seems not make sense as a date can't be simultaneously greater and lower than another date, so the criteria should be just [column M date] = [Expiry_Dates!D4]. Or maybe you have a startDate in Expiry_Dates!D4 and a endDate in another cell, say Expiry_Dates!E4, in this case just change the last occurrence of D4 to E4.