Adding cell contents to an array reference in macro

[KibblesJack]

After years of using base excel, I am just now learning how to use VBA to make my work process more efficient. I run weekly reports showing last weeks data, the financial weeks run Monday to Sunday. The data I pull contains data from the day I pull it, I don't want to include this data. I want to make my code reference a cell that contains the date I run the report so it can filter by this date and remove the data from the day I run the report. The "5/16/2022" date is what I want to change to be a cell reference that contains the current date. If this is not clear, please let me know.

ActiveSheet.Range("$A$1:$S$65000").AutoFilter Field:=18, Criteria1:=Array( _
        "="), Operator:=xlFilterValues, Criteria2:=Array(2, [COLOR=rgb(250, 197, 28)]"5/16/2022"[/COLOR])
    Range("A2081:S65000").Select
    Selection.ClearContents
    ActiveSheet.Range("$A$1:$S$65000").AutoFilter Field:=18

 

[Micron]

Have to think "5/16/2022" part would be Criteria2:=Array(2, Range("A2")).Value or whatever your cell address is.

 

[KibblesJack]

Have to think "5/16/2022" part would be Criteria2:=Array(2, Range("A2")).Value or whatever your cell address is.

Thanks Micron! That worked!

 

[Micron]

An easy one for a change!