Autofilter for Todays date using VBA

[dixon1983]

Hi,
I have a macro that I want to autofilter a sheet of data with the criteria being Todays date.

What criteria should I put in the line of code below and what format should the data in the column be in?

Selection.AutoFilter Field:=11, Criteria1:= ???

I have tried putting TODAY and NOW in there but it doesnt work.

Thanks for any help

 

[Andrew Poulsom]

Maybe try:

Selection.AutoFilter Field:=11, Criteria1:=Format(Date, Selection.Cells(2, 11).NumberFormat)

AutoFilter can be tricky with dates, so post back if that doesn't work, specifying the column's number format.

 

[dixon1983]

That didnt work unfortunately.

The format for that column is dd/mm/yyyy

 

[Andrew Poulsom]

It worked when I tried it.

What does this tell you?

MsgBox Selection.Cells(2, 11).NumberFormat

Are your dates integers or do they include a time?

 

[dixon1983]

It shows dd/mm/yyyy;@

Sub Hold()
Sheets("WsData").Select
Range("A1:K246").Select
Selection.AutoFilter
Selection.AutoFilter Field:=11, Criteria1:=Format(Date, Selection.Cells(2, 11).NumberFormat)
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Current Day on Hold").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End Sub

Thats the code im using

 

[Andrew Poulsom]

It still worked when I tried it with that number format.

Are your dates integers or do they include a time? What code do you get if you record a macro while AutoFiltering for today's date?

 

[dixon1983]

They are integers.

I got the same code as above but with "19/10/2006" in the criteria1 area when I recorded a macro

 

[Andrew Poulsom]

Try one of these:

Selection.AutoFilter Field:=11, Criteria1:=CLng(Date)

Selection.AutoFilter Field:=11, Criteria1:=">" & CLng(Date) - 1, Operator:=xlAnd, Criteria2:="<" & CLng(Date) + 1

 

[dixon1983]

Neither of them have worked

Thanks for your the time you're spending on this by the way, i appreciate it.

 

[Andrew Poulsom]

This is extremely frustrating (not your fault).

Does your recorded macro work when you run it?