1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Macro for filtering current month data

Discussion in 'Business Applications' started by rasik123, Dec 18, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. rasik123

    rasik123 Thread Starter

    Joined:
    Dec 17, 2008
    Messages:
    36
    Hi All,

    Can anybody write for the script for filtering the current month data from an excel sheet, irrespective of the month that we open the file. i.e. once I press the button assigned to macro, I need to filter records of current month from the DATE field that contains records of all months.
     
  2. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hello there!

    We need to know about your data structure. Where is the range your data is stored? You have headers, but on what row? Which row starts your data? How are your date values stored? Actual dates? And by "current month" do you mean the month of the system date? So right now we're in December, but come January 1 you want to filter for January? Can you perhaps provide a sample file?
     
  3. rasik123

    rasik123 Thread Starter

    Joined:
    Dec 17, 2008
    Messages:
    36
    My data has headers, S NO, Name, DOB, DOJ(Date of Joining), etc. It stars on A4 cell. current date means system date. I need a macro which filters all the people who joined company in current month (month of system date).
     
  4. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    In my opinion the easiest way to do this is to use a helper column to hold just the month and then filter on that column. If you want to do this manually you can just add the following formula to you first cell in the helper column and then copy down (just change A2 to reflect the first cell containing a date) before sorting or filtering.

    =month(a2)

    If you want the entire process automated try to post a sample workbook. Just replace any sensitive info with dummy data and one of us will write the code for you

    Regards,
    Rollin
     
  5. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Well, without knowing more, perhaps...

    Code:
    Sub myfilterforcurrentmonth()
        With ActiveSheet
            'assumes helper column in E, would be formula, example
            '    =TEXT(D5,"mmmm yyyy")    copy down as needed
            'the number 5 in the filter assumes column E houses the formula
            'change format here to match format in helper column formula
            .Range("A4:E" & .Cells(.Rows.Count, 1).End(xlUp).Row).AutoFilter Field:=5, Criteria1:=Format(Date, "mmmm yyyy")
        End With
    End Sub
    Take note of the comments, where this needs a helper column.

    HTH
     
  6. rasik123

    rasik123 Thread Starter

    Joined:
    Dec 17, 2008
    Messages:
    36
    Data file attached
     

    Attached Files:

  7. rasik123

    rasik123 Thread Starter

    Joined:
    Dec 17, 2008
    Messages:
    36
    Sub Macro1()
    '
    ' Macro1 Macro
    ' Macro recorded 19/12/2008 by RASIK
    '
    '
    Selection.AutoFilter Field:=3, Criteria1:=">=1/12/2008", Operator:=xlAnd _
    , Criteria2:="<1/1/2009"
    End Sub

    This is the macro where I have to change date fields manually each month. I need these to autamte this, does not want to change the above dates manually, instead system month's records should appear
     
  8. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Did you look at my post?
     
  9. rasik123

    rasik123 Thread Starter

    Joined:
    Dec 17, 2008
    Messages:
    36
    Yes, but it didn't work. I just need to bring system month in below macro and filter the data, instead of entering >=1/12/2008 and <1/1/2009. It should be equal to current month, or greater than last day of last month and less than first day of next month.

    Sub Macro1()
    '
    ' Macro1 Macro
    ' Macro recorded 19/12/2008 by RASIK
    '
    '
    Selection.AutoFilter Field:=3, Criteria1:=">=1/12/2008", Operator:=xlAnd _
    , Criteria2:="<1/1/2009"
    End Sub
     
  10. rasik123

    rasik123 Thread Starter

    Joined:
    Dec 17, 2008
    Messages:
    36
    But custom filter does not take from the cell value
     
  11. rasik123

    rasik123 Thread Starter

    Joined:
    Dec 17, 2008
    Messages:
    36
    Thanks a lot yaar, I had to put my brain in your code to solve my problem. Now I have got it, just needed to modify your code according to my data. Thank you very much once again
     
  12. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Ah, saw your file. This will work for you, and no need to select...

    Code:
    Sub FilterDOJCurrentMonth()
        With Sheets("Sheet1")
            With .Range("B1:D" & .Cells(.Rows.Count, "B").End(xlUp).Row)
                .AutoFilter field:=3, Criteria1:=">=" & DateSerial(Year(Date), Month(Date), 1), _
                    Criteria2:="<=" & DateSerial(Year(Date), Month(Date) + 1, 1) - 1
            End With
        End With
    End Sub
    Edit..

    Oh, and btw, if you ever have the need to take off the filter, you can use this...

    Code:
    Sub TakeFilterOffSheet1()
        Sheets("Sheet1").AutofilterMode = False
    End Sub
    HTH
     
  13. rasik123

    rasik123 Thread Starter

    Joined:
    Dec 17, 2008
    Messages:
    36
    This code brings the custom field conditions (current month) automatically, but no records are displaying. I have to go to the customer filter field and say OK..
     
  14. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    What do you mean? What happens exactly when you run the code? Can you either describe fully or post a screen shot?? It works for me just fine.
     
  15. rasik123

    rasik123 Thread Starter

    Joined:
    Dec 17, 2008
    Messages:
    36
    The problem is with my date format. I am using "16-Dec-08 " format. How do I change the format accordingly in code? When I changed the date format to "12/31/08", it works.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/780825

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice