Auto Filter on <Today - 7 Days> Macro in Excel

I have the following macro set up in Excel and I'd like to make the date that I autofilter on be dynamic so the date range is: >= today - (7days)

Any suggestions?


Sub Weekly_Status()
'
' Weekly_Status Macro
' Macro recorded 10/28/2003 by xxxxxxxxxxxxxxxx
'

'
    Rows("1:1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=2, Criteria1:="<>N/A", Operator:=xlAnd
    ActiveWindow.SmallScroll ToRight:=3
    Columns("G:I").Select
    Selection.NumberFormat = "mm/dd/yyyy;@"
    Selection.AutoFilter Field:=8, Criteria1:=">=10/21/2003", Operator:=xlOr _
        , Criteria2:="="
    ActiveWindow.SmallScroll Down:=-6
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.LargeScroll ToRight:=-1
End Sub
PerogiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ampapaCommented:
Give this a try...

Sub Weekly_Status()
'
' Weekly_Status Macro
' Macro recorded 10/28/2003 by xxxxxxxxxxxxxxxx
'

dim todaylessseven as string

todaylessseven = ">=" &date
'
    Rows("1:1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=2, Criteria1:="<>N/A", Operator:=xlAnd
    ActiveWindow.SmallScroll ToRight:=3
    Columns("G:I").Select
    Selection.NumberFormat = "mm/dd/yyyy;@"
    Selection.AutoFilter Field:=8, Criteria1:=todaylessseven, Operator:=xlOr _
        , Criteria2:="="
    ActiveWindow.SmallScroll Down:=-6
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.LargeScroll ToRight:=-1
End Sub
0
ampapaCommented:
Sorry the date filed was missing a space.

it should be - todaylessseven = ">=" & date
0
PerogiAuthor Commented:
okay, but your answer is just >= the current date.  I need it to be the current date minus seven days.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ampapaCommented:
Sorry If orgot to subtract 7. This should do it.

Sub Weekly_Status()
'
' Weekly_Status Macro
' Macro recorded 10/28/2003 by xxxxxxxxxxxxxxxx
'

dim todaylessseven as string

todaylessseven = ">=" & date -7
'
    Rows("1:1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=2, Criteria1:="<>N/A", Operator:=xlAnd
    ActiveWindow.SmallScroll ToRight:=3
    Columns("G:I").Select
    Selection.NumberFormat = "mm/dd/yyyy;@"
    Selection.AutoFilter Field:=8, Criteria1:=todaylessseven, Operator:=xlOr _
        , Criteria2:="="
    ActiveWindow.SmallScroll Down:=-6
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.LargeScroll ToRight:=-1
End Sub
0
PerogiAuthor Commented:
I thought the date calculation would be difficult you need to specify to subtract 7 days and not 7 years or 7 months.  Anyway, the above solution did not work.  I get the following error when running the macro:

"Autofilter method of Range class failed"

The debugger brings me to the line starting with: "Selection.AutoFilter Field:=8,"....
0
ampapaCommented:
I was basing my answer on what you had supplied. Sorry I was taking a short cut. I just ran this macro and it worked fine for me with date data in cell G1:G40. The date calculation is as easy as it appears.

Sub Weekly_Status()

Dim todaylessseven As String
MsgBox Date
MsgBox Date - 7

todaylessseven = ">=" & Date - 8
    Range("G1:G40").Select
    Selection.AutoFilter Field:=1, Criteria1:=todaylessseven, Operator:=xlOr _
        , Criteria2:="="

End Sub

ampapa,



0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PerogiAuthor Commented:
Awesome, it works now.  I just needed to add a test to see if autofilter was already on.  That is where I was getting my error message.  I was attempting to turn autofilter on on something that already was autofiltered.  Thanks for the help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.