Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Below is a listing showing rows that will be "overdue" by 2/28/10. I need to sort and print only those rows coming due in March, 2010, excluding Feb. and April. Next month, using 3/31/10, I want to do the same thing, sorting only those rows for April.

    Is there a macro I can use that will isolate, sort and print the data?

    Thanks.

    Assume the first column is A and the second B so 0/2/22/10 would be in A1.

    02/22/10 6
    02/23/10 5
    02/27/10 1
    02/27/10 1
    02/27/10 1
    03/08/10 -
    03/08/10 -
    03/09/10 -
    04/03/10
    04/05/10

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    You haven't mentioned what version of Excel you are using. However if you are using Excel 2007 there is a great filtering function, You can filter your list by date using two dates (it even gives you calendars to pick from). You can filter by date in the previous versions as well - just not as user friendly.
    After you have filtered the data you can sort and print what you see. (Actually, I'd probably begin with the sort - but that's me).
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry,

    I'm using Office 2003.

    Thanks.

  4. #4
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Unfortunately I'm using 2007 as well..

    As a starting point, please try running Macro1 in the attached file. If it works you should see only dates for Feb and I can build on from there.
    Attached Files Attached Files

  5. #5
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If it worked, thy this one:
    Code:
    Sub PrintNextMonth()
        Dim dStart As Date, dEnd As Date
        dStart = DateSerial(Year(Now), Month(Now()) + 1, 1)
        dEnd = DateSerial(Year(Now), Month(Now()) + 2, 0)
        
        Range("A1").AutoFilter Field:=1, _
        Criteria1:=">=" & dStart, Operator:=xlAnd, Criteria2:="<=" & dEnd
        '
        Range("A1").CurrentRegion.PrintOut
        Range("A1").AutoFilter
    End Sub

  6. #6
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Quote Originally Posted by wfjdi0r1 View Post
    Sorry,

    I'm using Office 2003.

    Thanks.
    Ah, just moved to a 2003 machine. Try Data, Filter. Once the Filter has been applied, use the drop-down button above the date column and select custom. Then you can filter for dates within a range.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  7. #7
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [attachment=88058:March Report.xls]

    Sorry, I'm not understanding what to substitute in the macro so I've attached the file. Could anyone post a macro that will allow me to sort and print only the next month's entries; in this case March.

    Thanks.
    Attached Files Attached Files

  8. #8
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    There are two macros. the first will calculate the date range based on the current date. When run in February,March dates on the print out. The second macro prints the data for the month following the date entered in cell E2.

    Code:
    Sub PrintNextMonth()
    'prints based on current date
        Dim dStart As Date, dEnd As Date
        dStart = DateSerial(Year(Now), Month(Now()) + 1, 1)
        dEnd = DateSerial(Year(Now()), Month(Now()) + 2, 0)
        
        Range("c8").AutoFilter Field:=3, _
        Criteria1:=">=" & dStart, Operator:=xlAnd, Criteria2:="<=" & dEnd
        '
        Range("c8").CurrentRegion.PrintOut
        Range("c8").AutoFilter
    End Sub
    
    
    Sub PrintNextMonth2()
    'prints based on cell E2
        Dim dStart As Date, dEnd As Date
        dStart = DateSerial(Year(Cells(2, 5)), Month(Cells(2, 5)) + 1, 1)
        dEnd = DateSerial(Year(Cells(2, 5)), Month(Cells(2, 5)) + 2, 0)
        
        Range("c8").AutoFilter Field:=3, _
        Criteria1:=">=" & dStart, Operator:=xlAnd, Criteria2:="<=" & dEnd
        '
        Range("c8").CurrentRegion.PrintOut
        Range("c8").AutoFilter
    End Sub
    Attached Files Attached Files

  9. #9
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I apologize - I forgot to add the sort routine to the macros.

    Code:
    Sub PrintNextMonth()
    'based on date run
        Dim dStart As Date, dEnd As Date
        dStart = DateSerial(Year(Now), Month(Now()) + 1, 1)
        dEnd = DateSerial(Year(Now), Month(Now()) + 2, 0)
        Range("c8").CurrentRegion.sort key1:=Range("c8"), Header:=xlYes, Order1:=xlAscending
    
        Range("c8").AutoFilter Field:=3, _
        Criteria1:=">=" & dStart, Operator:=xlAnd, Criteria2:="<=" & dEnd
        '
        Range("c8").CurrentRegion.PrintOut
        Range("c8").AutoFilter
    End Sub
    
    
    Sub PrintNextMonth2()
    'based on date entered in E2
        Dim dStart As Date, dEnd As Date
        dStart = DateSerial(Year(Cells(2, 5)), Month(Cells(2, 5)) + 1, 1)
        dEnd = DateSerial(Year(Cells(2, 5)), Month(Cells(2, 5)) + 2, 0)
        Range("c8").CurrentRegion.sort key1:=Range("c8"), Header:=xlYes, Order1:=xlAscending
    
        Range("c8").AutoFilter Field:=3, _
        Criteria1:=">=" & dStart, Operator:=xlAnd, Criteria2:="<=" & dEnd
        '
        Range("c8").CurrentRegion.PrintOut
        Range("c8").AutoFilter
    End Sub

  10. #10
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks. I was just going to ask about the sort. It seems to work fine now.

Posting Permissions

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