Dynamic Auto Filter in macro

Hi, question:

Here is my code:

Sub AutoFilter()

Sheets("Feb 18 - Feb 22").Select
Selection.AutoFilter
ActiveWindow.LargeScroll ToRight:=-1
Selection.AutoFilter Field:=1, Criteria1:="02/20/02"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter
End Sub


In the bold section ("02/20/02") my macro does an auto filter by that date. I want the date to reflect TODAY'S date, but I can't get it to work. Instead of "02/20/02", I've tried:
Today
Format(Today, "mm/dd/yy")
and tried linking it to a cell that contains today's date, but none of these work...

Some help! please!

Thanks

[Juan Pablo González]

Today doesn't exist in VBA, try with Date instead.

No, Date doesn't work either Any other suggestions?

[Juan Pablo González]

Try with

Criteria1:=Format(Date,"mm/dd/yy")

or

Criteria1:=Date

or

Criteria1:=CStr(Date)

or

Criteria1:=Format(Date,"dd/mm/yy")

I tried all of those, still nothing. I also tried linking to a cell and that didnt work either. The filter comes on, but it displays nothing. There ARE dates from today in the list, if I put "02/20/02" it works fine. Im using excel 2002, and my column is formatted as mm/dd/yy (it displays as m/dd/yyyy in the formula bar). Is this just not possible what I'm trying to do? or is there maybe another way of going about it? I appreciate the help

[Derek]

Hi
In C3 type formula =today()
Refer to it in your code like this

Selection.AutoFilter Field:=1, Criteria1:=Range("C1").Value

This works fine for me.
Hope it helps
Derek
ps Oh, one snag, you will have to reformat your dates and the formula in C1 to text before you start and convert back afterwards. This converts the date to a number. I guess you can do this in your macro code.


[ This Message was edited by: Derek on 2002-02-21 02:22 ]

[Juan Pablo González]

Try this other one, I think this WILL work

Criteria1:=CLng(Date)

Strike 5 Juan! LoL, i would have liked to incorporate a way to do this in my code, but Thanks Derek, looks like your solution works.

Thanks for the help guys