VBA code to filter as per date

[Sameer.k21]

Hi,

I have this code which works perfect. However I now have about 40files and each file will have different start dates. Do I have to go to each file and specify the start date in the range for this code to pick up or is there another way around?

Public Sub MyFilter()
    Dim lngStart As Long, lngEnd As Long
    lngStart = Range("AA4").Value 'assume this is the start date
    lngEnd = Range("AA5").Value 'assume this is the end date
    Range("E:E").AutoFilter field:=1, _
        Criteria1:=">=" & lngStart, _
        Operator:=xlAnd, _
        Criteria2:="<=" & lngEnd
      
End Sub

Regards,
Sameer

 

[Stevie]

I don't really understand exactly what you are trying to do, but it sounds like you don't want to type dates into AA4 and AA5 each time you run your macro on a new workbook.
You could replace the cell references with an input box (google vba inputbox) so that you can type the dates in a pop-up, but that is almost the same thing.
A simple solution might be to have another workbook open whilst you go through your process, which has a list of file names and dates in columns. If filename was in column A, start date in column b and end date in column c, all in sheet 1, in a workbook called "Dates" then you could do this:

Public Sub MyFilter()
    Dim lngStart As Long, lngEnd As Long, rownum As Integer
    rownum = Application.WorksheetFunction.Match(ActiveWorkbook.Name, Workbooks("Dates").Sheets("Sheet 1").Range("A:A"), 0)
    lngStart = Workbooks("Dates").Sheets("Sheet 1").Range("B" & rownum).Value 'read start date from Dates workbook
    lngEnd = Workbooks("Dates").Sheets("Sheet 1").Range("C" & rownum).Value  'read end date from Dates workbook
    Range("E:E").AutoFilter field:=1, _
        Criteria1:=">=" & lngStart, _
        Operator:=xlAnd, _
        Criteria2:="<=" & lngEnd
     
End Sub

... or something similar.

I hope this helps.

 

[Sameer.k21]

Sorry for the delay @Stevie

This code runs however excludes current date.

Range("E:E").AutoFilter field:=1, _
        Criteria1:=">=" & lngStart, _
        Operator:=xlAnd, _
        Criteria2:="<=" & lngEnd

 

[Deepak]

Sameer.k21

Check Something like this!!

Tested!!!

Dim lngStart As Date, lngEnd As Date

lngStart = Format(DateSerial(2015, 10, 23), "YYYY-MM-DD")

lngEnd = Format(DateSerial(2016, 1, 4), "YYYY-MM-DD")

Range("E:E").AutoFilter field:=1, _
        Criteria1:=">=" & lngStart, _
        Operator:=xlAnd, _
        Criteria2:="<=" & lngEnd

 

[Sameer.k21]

Thanks @Deepak so the date format was the problem

 

[Deepak]

Thanks @Deepak so the date format was the problem

have you checked that!!

 

[Sameer.k21]

have you checked that!!

Yes, I changed the reference cell date format and it worked. Thank you @Stevie and @Deepak