macro filter column for most recent date
 

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

macro filter column for most recent date
 

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

macro filter column for most recent date
 

Cells.Select
Dim dt as Date
' the next line assumes the date column is in the column to the right of
the upper
' left corner of the selection. Adjust to suit.
dt = clng(Application.Max(selection(1).offset(0,1).Enti reColumn))
Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:="HHP"
Selection.AutoFilter Field:=3, Criteria1:="Open"
Selection.AutoFilter Field:=2, Criteria1:="=" & format(dt,"yyyy/mm/dd")
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

--
Regards,
Tom Ogilvy


"flymo" wrote:

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

macro filter column for most recent date
 

On Jun 20, 9:02 am, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
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"
Bernie,
Worked like a charm.
Many Thanks

John



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- Hide quoted text -

- Show quoted text -