CODEWITHSUNDEEP
excelvba
user13842516
user13842516

Reputation:

=TODAY() Function not working with code for date criteria

Below is the code I am working with to create a new table based on criteria (department and date) from an original table. The MaxDate works when I put in a specific date "8/4/2020", but not when I try and use the "=TODAY()" function. Any ideas?

Sub DepartmentSearch()
    
    Department = "IT"
    MaxDate = "=TODAY()"

    Set rng = ActiveSheet.UsedRange  ' source table
    rng.AutoFilter Field:=13, Criteria1:=Department  ' filter Department
    ActiveSheet.UsedRange.AutoFilter Field:=8, Criteria1:="<" & MaxDate  ' filter date
    rng.Copy  ' copy filtered rows
    Range("Z2").Select ' source table
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False  ' paste values to target table
    rng.AutoFilter ' turn off filter on source table

End Sub

Upvotes: 0

Views: 156

Answers (1)

BigBen
BigBen

Reputation: 49998

You are conflating formulas and VBA.

That said, just use Date:

MaxDate = Date

Upvotes: 1

Related Questions