Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sort by date/time distributing to separate sheets (XP)

    I have a similar question to this one. <post#=203924>post 203924</post#>

    However, this time, I only need it to distribute to two separate sheets. One for the current day, and the other covering the remainder.

    Anything prior to 6:PM on the current date should be removed.
    The window from 6:PM today through 5:59 PM the following day needs to be distributed onto the first sheet, and anything 6:00 PM the following day and after, would go to the second sheet.
    If possible, I'd like the sheet containing the current date to be named the that date, and the second sheet the range of dates it covers.

    I'm uploading an example of the data.

    Thanks in advance for the help.
    Attached Files Attached Files

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Sort by date/time distributing to separate sheets (XP)

    I had a quick shot at this, but I'll have to ask you to ensure it does exactly what you want and possibly do some debugging.

    Sub Macro1()
    Dim strBefore As String, strCurrent As String, strNext As String
    strBefore = "<" & CStr(Date) & " 18:00"
    strCurrent = ">=" & CStr(Date) & " 18:00"
    strNext = CStr(Date + 1) & " 18:00"
    ' build worksheets
    Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
    Worksheets(Worksheets.Count).Name = Replace(CStr(Date), "/", "-")
    Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
    Worksheets(Worksheets.Count).Name = Replace(CStr(Date + 1), "/", "-")
    ' run filters
    With Worksheets("Sheet1").Range("A1:W" & Worksheets("Sheet1").UsedRange.Rows.Count)
    ' filter for and delete old
    .AutoFilter Field:=8, Criteria1:=strBefore
    Rows("2:" & Worksheets("Sheet1").UsedRange.Rows.Count).Delete Shift:=xlUp
    ' filter for and copy current
    .AutoFilter Field:=8, Criteria1:=strCurrent, Operator:=xlAnd, Criteria2:="<" & strNext
    .CurrentRegion.SpecialCells(xlCellTypeVisible).Cop y _
    Worksheets(Replace(CStr(Date), "/", "-")).Cells(1, 1)
    ' filter for and copy future
    .AutoFilter Field:=8, Criteria1:=">=" & strNext
    .CurrentRegion.SpecialCells(xlCellTypeVisible).Cop y _
    Worksheets(Replace(CStr(Date + 1), "/", "-")).Cells(1, 1)
    End With
    Worksheets("Sheet1").AutoFilterMode = False
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort by date/time distributing to separate sheets (XP)

    This does exactly as I need except the name of the second sheet is the next day rather than the range of dates it covers. I may not have been clear when giving that description. I can live with it and probably figure out the rest from here.

    Thank you very much. You have saved me much time.

    <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>

Posting Permissions

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