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]

It is always easier to help and test possible solutions if we could work with your actual file. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[JustAGuy5]

So something odd happened. I deleted the code and repasted it, and now I have a different error. I get the following message:

Run-time error '-2147417848 (80010108)':
Method 'Find' of object 'Range' failed.

Here is what I see when I click debug:

If I post the actual file... will I need to post the linked file as well?

 

[mumps]

I would need both files to test.

 

[JustAGuy5]

I would need both files to test.

Hi mumps,

I am attaching both files to my dropbox. You can access them here:

https://www.dropbox.com/s/fadta8sq83m0pnl/Combo%20Status%20Database.zip?dl=0

There are two files in there. One is the Combo Change Status Database (CCSD) and the other is the Spring 2018 Vacancy Report - TEST (VR). The file that I am trying to auto sort with the code you provided is the CCSD. What we're trying to do is to have the data from column B (Room Change Date) auto sort upon opening the file; however, the data in columns A & B are not manually entered. That data is linked to columns L & M from each spreadsheet within VR. The way it works, is that a user will manually input data under column J (Room Change) in any of the spreadsheets in VR. Then, IF formulas will then generate data in columns L & M, which will then be displayed in the linked cells within CCSD.

I removed the code from the CCSD because it kept freezing up Excel, especially when opening the file. In order to recreate the bug, you will need to enter the code.

I hope all of this makes sense. I know it is confusing, especially considering that I may be using the wrong terminology. I have to say that I am blown away by your kindness and willingness to share your expertise. So, THANK YOU again for all that you have done to help me.

MyBest,
JustAGuy5

PS. This is the second time I am trying to post this. The first time I did it, I must've logged out and was taken back to the login page. I lost everything. ARG!

 

[mumps]

I would need some clarification. You say that you want to

auto sort upon opening the file.

In your original post you said:

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

This involves two different processes. Which would you like to do? You can do one or the other or both.

 

[JustAGuy5]

In a perfect world, both. But if that's more complicated and less likely for it to work, sorting upon opening is what we really need, as it is far less likely that the spreadsheet be updated while someone is in it.

Were you able to observe the bug? Also, where did you learn all of this? I want to be an excel God like you!

 

[mumps]

I have to go out for an appointment. I will get back to you as soon as I can.

 

[mumps]

Thank you for the kind words. I'm not so sure, though, about "Excel God"!!!!

Take the following steps:

Place the macro below in the code module for ThisWorkbook in the "Combo..." workbook. Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the left hand pane, double click on "ThisWorkbook". Copy/paste the macro into the empty window that opens up. Close the window to return to your sheet. This macro will do the sorting when you open the workbook.

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Sheets("Sheet1").Sort.SortFields.Clear
    Sheets("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

Copy and paste the macro below into the worksheet code module. Do the following: right click the tab for your Sheet1 of the "Combo..." workbook and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. This macro will do the sorting every time there is a change in column J of any sheet in the "Spring...." workbook. Make sure the links in column B of Sheet1 of the "Combo..." workbook are accurate and up-to-date.

Private Sub Worksheet_Calculate()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim WB As Workbook
    Set WB = Workbooks("Combo Change Status Database.xlsm")
    Dim ws As Worksheet
    Set ws = WB.Sheets("Sheet1")
    Dim LastRow As Long
    LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    ws.Sort.SortFields.Clear
    ws.Sort.SortFields.Add Key:=Range("B2:B" & LastRow), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ws.Sort
        .SetRange Range("A1:D" & LastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

As far as learning VBA, the more you use it the more you learn. Searching online is a great resource. The very first post (highlighted in blue) of this Forum is a very valuable resource. This link from hiker95 is also a valuable resource: https://www.mrexcel.com/forum/excel...e-template-excel-spreadsheet.html#post4972842

I would suggest that you start a library of useful macros, codes and formulas and add to it as you go along. You can then refer to this library any time you may need code that you have already used. Please let me know how it works out.

 

[JustAGuy5]

That worked.... SO PERFECTLY!!!! You are more than an excel God, you're my SAVIOR! Thank you so much for everything that you have done. I cannot put into words how grateful I am. Your kindness and willingness to help a total stranger just blows me away. Thank you again for everything. Also, great advice! I'm going to start making a library. I just hope one day I'll be able to help someone else like you helped me!

Be Well My Friend,
Graz

 

[mumps]

You are very welcome. I'm glad it all worked out.