Coding Text Filters in Excel 2007

Question

I am trying to use code to filter a spreadsheet dynamically, based on the current date.

I am storing the date I need to filter on as "CurrDay" and I'm attempting to recall that stored date back into my filter algorithm. It is not working and I need to figure out how to do this to finish up this code. Everytime I run the code it returns the CurrDay name in the filter instead of the date stored under the CurrDay variable.

I'm missing something here and I need some direction. Any help is appreciated.

CODE:

Sub Finishing_A59_Filter()
'
' Finishing_A59_Filter Macro

' This macro will activate the A59 and Filter it properly for standard orders
' 

'This macro does not include the VMI's and APS orders in the code
' 
'

Dim Currday As Date

    Currday = Date + 7
    UName = Application.UserName


    Workbooks.Open Filename:="G:\Copy Modified A59 5-19-2009.xlsm", UpdateLinks _
        :=0
    Range("M2").Select
    ActiveCell.Value = Currday

    Columns("Q:Q").Select
    Selection.NumberFormat = "mm/d/yyyy"

    ' Filter the sheet to remove VMI's and APS orders

    ActiveSheet.Range("$A$3:$AA$2941").AutoFilter Field:=23, Criteria1:=Array( _
        "01", "04", "06", "08", "09", "10", "15", "25", "="), Operator:=xlFilterValues

   ' Set the proper date range for the sheet - This needs to be seven days beyond the current date

    ActiveSheet.Range("$A$3:$AA$2941").AutoFilter Field:=17, Criteria1:= _
        "<=Currday", Operator:=xlAnd

Answer 1

You need to concatenate the variable with the criteria string.

ActiveSheet.UsedRange.AutoFilter Field:=17, Criteria1:= _
        "<=" & Currday, Operator:=xlAnd

Also, it's better to use "UsedRange" instead of making up a big range because it doesn't work if your data extends past your arbitrary range and is a waste of resources if it doesn't.