# Auto sorting in Excel



## LauraN (May 25, 2011)

I have been trying to figure this out myself, but am just not getting there - too new at all this. I need just a very basic auto sort in Excel, for a basic spreadsheet that currently has columns A thru S, with column S being the column that will need to be sorted by. I would like the auto sort to be done when the document is saved or upon close. Currently the data in column S has a "x" or nothing, but I can put in a date if that is easier to sort by.

I haven't used the auto sort feature before, but I would love to mess with this a little once I can figure it out and see what will work best for me.

Thanks for any help and let me know if you need more information from me.

Laura


----------



## JMPC (Jan 15, 2011)

See if this helps:
http://www.techsupportforum.com/forums/f57/can-someone-help-me-auto-sort-in-excel-252123.html

Auto Sort Columns on Workbook Open - Excel Help Forum


----------



## RSpecianjr (Jan 20, 2010)

Hey Laura,

Easiest way to do it is to record a macro then transfer the recorded code into the event you want.

In the bottom left hand corner of excel there is a button to start recording. When you press this, if you press it again, it will stop the recording.

Figure out how you are going to sort it then hit record and go through the manual steps of sorting.

If you use the sort function that is built in to excel (as opposed to autofilters) get the sort as close to end result as possible. You can change the key range when you look at the code. Once you have done the sort manually, just hit the stop button for the macro recorder.

Open up the VBA Editor (Alt+F11).

On the left hand side, expand the Modules folder and double click on the newest module (likely Module 1). You should see something like:



> Sub Macro2()
> '
> ' Macro2 Macro
> '
> ...


The only part we care about is:



> ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
> ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("S20"), _
> SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
> With ActiveWorkbook.Worksheets("Sheet1").Sort
> ...



Things you would want to change:

Key:=Range("S20") to the destination range, so if you are sorting in column S and you have a header, it should be S2.

.SetRange Range("A2:S20") will change depending on how much data you have.

To set it up to run on an event, you will want too click on the ThisWorkbook Excel Object on the left hand. This will open a blank page and give you two dropdowns at the top. Select Workbook from the dropdown on the left and the event you want from the right. The two you would want are:

BeforeSave

and 

Open

This will create a open and end tags for that event. Just paste your code in there and try it out.

Regards,

Robert D. Specian Jr.


----------

