Autosort spreadsheet according to Date

[JustAGuy5]

Hi Folks,

I want to sort a spreadsheet that links information from another spreadsheet. I want to sort it by the Room Change Date column, and have the other columns sort with it as well.

Here's a sample of what I'm trying to do:

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD]Room Number[/TD]
[TD]Room Change Date[/TD]
[TD]Combo Change Date[/TD]
[TD]Comments[/TD]
[/TR]
[TR]
[TD]Connor 105[/TD]
[TD]10/1/2017[/TD]
[TD]10/5/2017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Connor 225[/TD]
[TD]3/4/2018[/TD]
[TD]3/5/2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Connor 115[/TD]
[TD]3/11/2018[/TD]
[TD]11/3/2017[/TD]
[TD]NEEDS TO BE UPDATED[/TD]
[/TR]
[TR]
[TD]Connor 301[/TD]
[TD]6/11/2018[/TD]
[TD][/TD]
[TD]

NEEDS TO BE UPDATED​

<strike></strike>[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
I would like it so that anytime a new date is listed under the Room Change Date, the spreadsheet will automatically sort the Room Change Date in chronological order. Is there a way to do this without having to constantly use the sort function?

Thanks for any help in advance!
JustAGuy5

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a date in column B and exit the cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B2:B" & LastRow), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:D" & LastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Application.ScreenUpdating = True
End Sub

 

[JustAGuy5]

mumps, I have spent HOURS trying to learn how to do this from watching youtube videos, and you solved my problem in less than 10 minutes. Thank you, Thank You, THANK YOU FOR SHARING YOUR EXPERTISE!!!!!!

 

[JustAGuy5]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a date in column B and exit the cell.

Hi mumps, so I tried it out, and it works! So thanks again for everything you've done. I'm just curious, but is there a way for it to sort without going into a cell under column B? I only ask this because data in that column will not be entered by the person viewing the spreadsheet. That info is populated by another spreadsheet that it is linked to. So in order for the user to see the proper sorted list, they need to click into a cell in column B and then click out of it for it to work. This isn't the end of the world, but I want to be sure that users won't forget to do that and inadvertently miss critical data that may be well below what is displayed on their screen. Thanks again for everything!

 

[mumps]

Are you saying that the dates in column B are the result of a formula rather than being entered manually?

 

[JustAGuy5]

Hi mumps,

Yes, that is exactly what I am saying. The dates will not be data entered in column B manually. They will be populated by another spreadsheet that is linked to it.

Best,
JustAGuy5

 

[mumps]

Try:

Private Sub Worksheet_Calculate()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B2:B" & LastRow), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:D" & LastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Application.ScreenUpdating = True
End Sub

 

[JustAGuy5]

Hi mumps,

I tried that and got a bug mess for this line:

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear

Any ideas what needs to be fixed? Thanks again for your help. I can't tell you how much I appreciate this.

 

[mumps]

Is the sheet named "Sheet1"?

 

[JustAGuy5]

Yes it is.