How can I have data re-sort when a file opens?

I have a workbook with data that I'd like to re-sort (randomly, if that matters) every time the workbook is opened. (For instance, re-sorting by column B, generated by a rand() call, in the workbook pictured below, every time the file is opened.)

I'm no script wiz, but it doesn't seem that either of

• Excel 2013, can data be re-sorted automatically?
• Automatic sort for excel worksheet

quite get there. (If I'm reading correctly, they're talking about updating things when an activity is taken within the sheet: for my purpose it's important that the re-sort happens when the sheet is first opened.)

I assume there's something like a four-line script that I could write to do it, but I have no idea how.

How can I re-sort a workbook on file open?

_{[Excel version 14.0.7180.5002, part of office 2010.]}

_{In case anyone's curious, the use is: I'm sending a list to my community of a list of candidates to vote for; I'd like the list presented randomly, so as not to bias votes toward the top of the stack. (There are over a hundred candidates, and only one vote to cast.)}

In VBA, this can be done with one line of code in one subroutine:

Private Sub Workbook_Open()

    Sheet1.Range("A:B").Sort Key1:=Sheet1.Range("B:B"), Order1:=xlAscending, Header:=xlYes

End Sub

(Replace each instance of "sheet1" with the name of your worksheet (not workbook))

Layman's explanation: The Workbook_Open event happens when the Excel workbook is opened and runs once. Within there, the one line of code is basically saying: Use the sort method on this area of cells based on column B. Sort the results ascending. Ignore the top row because it's a header.

Note that the values in column B won't appear sorted. This is because Excel is recalculating the rand() function twice - Once when the workbook opens, and once when the data is sorted. You could add more code to control when the calculations happen, but in your case, it doesn't seem to matter; the end result is that the names are in random order.

Step-by-step on how to get this working:

1. Save your workbook as a macro enabled Workbook. File > Save As > Change "Save As Type" dropdown to "Excel Macro Enabled Workbook (*.xlsm).

2. Enable the developer tab. File > Options > Customize Ribbon > Check off "Developer" under main tabs. Press OK.

3. Developer tab > click "Visual Basic". A new window opens. Right click on "ThisWorkbook" and left-click "View Code".

4. Paste the subroutine into the code area on the right. Change the worksheet object name from sheet1 to whatever it should be. Save, close, reopen, and you should see it randomly sort the names.

As far as the underneath-the-hood stuff and the inner workings of VBA, there are plenty of resources on the internet. Unfortunately I still do a little bit of it in my professional life, and have "Mastering VBA for Microsoft Office 2010" by Sybex as a good desk reference.

If you want to avoid using VBa, the simplest solution is to use a pivot table.

Select your columns, insert a Pivot Table in a new sheet, arrange the columns in "Tabular form" and use the manual filter (descending or ascending,it doesn't matter) on the one with the randomized number then go to the Options tab (of the Pivot Table), select Options and look for the Data sub-tab, click on "actualize data on opening".

You can hide the first sheet containing the source data if you want.

Voilà!

Every time you open the file the Pivot table will reorder.