VBA to filter by dates, return values only if between certain dates

Hi All,

I have a list of employee names working on a job, beside their names is a start date and end date.

Column A: Name
Column B: Start Date
Column C: End Date

I want to write a macro that will look at the start date and end date, and if today's date falls between those dates, I want to copy and paste the name to a separate sheet

Perhaps there is a way to use filter in my macro to achieve this result, as if it returns a blank, I don't want that blank to be copied to the new sheet.

I can upload a test workbook if that will help.

Thanks in advance

Further to the last post, I am able to filter the start date to "before or equal to" today

and the end date to "After or equal to" today

Recording this macro returns the following code

ActiveSheet.ListObjects("Table_owssvr_1").Range.AutoFilter Field:=5, _
        Criteria1:="<=24/05/2015", Operator:=xlAnd
    ActiveSheet.ListObjects("Table_owssvr_1").Range.AutoFilter Field:=6, _
        Criteria1:=">=24/05/2015", Operator:=xlAnd

However I need the date "24/05/2015" to always be the current date.

Any help be great

Thanks

Hi,

Thanks for your reply, however this seems a bit intricate for what I am looking for. As it will be inexperienced users using this macro.

I think I have found a workaround my problem using the following code, however this is not running well for me

Sub Dates()

Dim DateCell As Date
DateCell = Worksheets("Date").Range("A1")

    Worksheets("Histogram").ListObjects("Table_owssvr_1").Range.AutoFilter Field:=5, _
        Criteria1:="=<DateCell", Operator:=xlAnd
    Worksheets("Histogram").ListObjects("Table_owssvr_1").Range.AutoFilter Field:=6, _
        Criteria1:="=>DateCell", Operator:=xlAnd
        
End Sub

When I run this, weather I enter physical dates, or look at the selected cell, the filters do not return anything, although if I filter manually it does.

No idea what is causing this, I can record the macro, and when recording I get the results I want, but when I run the macro it returns empty.

Any ideas whats causing this?

Hi,

Thanks for your reply, however this seems a bit intricate for what I am looking for. As it will be inexperienced users using this macro.

I think I have found a workaround my problem using the following code, however this is not running well for me

Sub Dates()

Dim DateCell As Date
DateCell = Worksheets("Date").Range("A1")

    Worksheets("Histogram").ListObjects("Table_owssvr_1").Range.AutoFilter Field:=5, _
        Criteria1:="=<DateCell", Operator:=xlAnd
    Worksheets("Histogram").ListObjects("Table_owssvr_1").Range.AutoFilter Field:=6, _
        Criteria1:="=>DateCell", Operator:=xlAnd
        
End Sub

When I run this, weather I enter physical dates, or look at the selected cell, the filters do not return anything, although if I filter manually it does.

No idea what is causing this, I can record the macro, and when recording I get the results I want, but when I run the macro it returns empty.

Any ideas whats causing this?