Dynamic Auto Filter in macro
Dynamic Auto Filter in macro
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Dynamic Auto Filter in macro

  1. #1
    Guest

    Default

     
    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

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Today doesn't exist in VBA, try with Date instead.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    Guest

    Default

    No, Date doesn't work either Any other suggestions?

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try with

    Criteria1:=Format(Date,"mm/dd/yy")

    or

    Criteria1:=Date

    or

    Criteria1:=CStr(Date)

    or

    Criteria1:=Format(Date,"dd/mm/yy")

    Regards,

    Juan Pablo González
    http://www.juanpg.com

  5. #5
    Guest

    Default

    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

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this other one, I think this WILL work

    Criteria1:=CLng(Date)
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  8. #8
    Guest

    Default

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com