John,

Use this in place of your field 2 code:

Selection.AutoFilter Field:=2, Criteria1:="1", Operator:=xlTop10Items

HTH,
Bernie
MS Excel MVP


"flymo" wrote in message
oups.com...
Helo,
I have a macro running to filter a series of values and works fine so
far. However, I need to filter a column of dates and only need to
select records with the most recent date (I can't make the field =
NOW() to get current date as the records may include today but also
may be a few days old.

Cells.Select
Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:="HHP"
Selection.AutoFilter Field:=3, Criteria1:="Open"
Selection.AutoFilter Field:=2, Criteria1:="2007/06/14 21:00" '
this is the most current right now - so may change day to day
Range("A171:AV171").Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Selection.Columns.AutoFit


Many thanks
John