PDA

View Full Version : Solved: Filtering records older than one year ago



slang
09-24-2009, 10:23 AM
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
09-24-2009, 11:00 AM
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
09-24-2009, 11:28 AM
The Today() line comes up as function not defined.

Bob Phillips
09-24-2009, 11:42 AM
Didn't notice that, use Datae not Today() in VBA.

slang
09-25-2009, 04:01 AM
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
09-25-2009, 06:06 AM
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
09-25-2009, 06:24 AM
Once again.
Thanks!!!!!!:beerchug: