Dynamic Auto Filter in macro

G

Guest

Guest
Hi, question:

Here is my code:

Sub AutoFilter()

Sheets("Feb 18 - Feb 22").Select
Selection.AutoFilter
ActiveWindow.LargeScroll ToRight:=-1
Selection.AutoFilter Field:=1, Criteria1:="02/20/02"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Selection.AutoFilter
End Sub


In the bold section ("02/20/02") my macro does an auto filter by that date. I want the date to reflect TODAY'S date, but I can't get it to work. Instead of "02/20/02", I've tried:
Today
Format(Today, "mm/dd/yy")
and tried linking it to a cell that contains today's date, but none of these work...

Some help! please!

Thanks :)
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
I tried all of those, still nothing. I also tried linking to a cell and that didnt work either. The filter comes on, but it displays nothing. There ARE dates from today in the list, if I put "02/20/02" it works fine. Im using excel 2002, and my column is formatted as mm/dd/yy (it displays as m/dd/yyyy in the formula bar). Is this just not possible what I'm trying to do? or is there maybe another way of going about it? I appreciate the help :)
 
Upvote 0
Hi
In C3 type formula =today()
Refer to it in your code like this

Selection.AutoFilter Field:=1, Criteria1:=Range("C1").Value

This works fine for me.
Hope it helps
Derek
ps Oh, one snag, you will have to reformat your dates and the formula in C1 to text before you start and convert back afterwards. This converts the date to a number. I guess you can do this in your macro code.
This message was edited by Derek on 2002-02-21 02:22
 
Upvote 0
Strike 5 Juan! LoL, i would have liked to incorporate a way to do this in my code, but Thanks Derek, looks like your solution works.

Thanks for the help guys :)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top