Solved: Filtering records older than one year ago

[slang]

I got the code down to use the autofilter but am having problems with the date format for the filter.

There must be a one liner to give me a date in the MM/DD/YYYY format like Filterdate=today()-365
This is what I got so far:banghead:



Dim curyear As Date
curyear = today() - 365
Format(curyear, "mm/dd/yyyy")
Workbooks.Open Filename:="C:\BDR\33activity.xls"
Application.Goto Reference:="data"
Selection.AutoFilter
Selection.AutoFilter Field:=5, Criteria1:=curyear, Operator:=xlAnd


Just having one of those days again!:mkay

[Bob Phillips]

I tend to use code like this



Dim curyear As Date
curyear = today() - 365
Workbooks.Open Filename:="C:\BDR\33activity.xls"
Application.Goto Reference:="data"
Selection.AutoFilter
Selection.AutoFilter Field:=5, Criteria1:=Format(curyear, Selection.Cells(2,1).NumberFormat) , Operator:=xlAnd

[slang]

The Today() line comes up as function not defined.

[Bob Phillips]

Didn't notice that, use Datae not Today() in VBA.

[slang]

Sorry, it does not return any data.:(
Would it be because of the time in the data's date field?

I would like to filter only records that are equal to or greater than one year ago essentially providing a rolling year of data.

I attached one of the data files with the actual data if this helps.

Thanks again, and again, and again :bow: :bow: :bow:

This is the code so far,


Sub Macro4()
Dim curyear As Date
curyear = Date - 365
Workbooks.Open Filename:="C:\BDR\33activity.xls"
Application.Goto Reference:="data"
Selection.AutoFilter
Selection.AutoFilter Field:=5, Criteria1:=Format(curyear, Selection.Cells(2, 1).NumberFormat), Operator:=xlAnd
Selection.Copy
End Sub
</IMG></IMG></IMG></IMG>

[mdmackillop]

Sub Macro4()
Dim curyear As Date
curyear = Date - 365
Workbooks.Open Filename:="C:\BDR\33activity.xls"
Application.Goto Reference:="data"
Range("data").AutoFilter Field:=5, Criteria1:=">" & CLng(curyear)
Range("data").Copy
End Sub

[slang]

Once again.
Thanks!!!!!!:beerchug: