# Auto Sorting Using Macros



## floyd1987 (Dec 22, 2014)

Hello. I've recently discovered how to use macros. Specifically, how to have a worksheet automatically sort section of the worksheet anytime new data is input into some preassigned column(s). The problem is that the data I want automatically/continuously sorted draws information that the user inputs in other parts of the workbook. I was wondering if there was a way to setup a macro to automatically sort a section of a worksheet anytime ANY change is made in a workbook by the user? OR, how can I setup a macro so that it continuously sorts a section of a worksheet the moment it's opened, regardless of whether the user inputs data or not? (I suspect the latter would be less taxing on the system that opens the workbook, but, really, either one would fit my purposes).

I can't seem to find any articles online regarding what I want, nor can I seem to find anything regarding the "programming" portion of using Microsoft Excel (which is what a macro is). Any pointers in educational material would also be much appreciated.

Thank you very much for your assistance.


----------



## Corday (Mar 3, 2010)

Rename the Macro you created and ran Autpen. This will sort on opening.


----------



## floyd1987 (Dec 22, 2014)

This would be an almost perfect solution...

Only Problem:

I would *like* for the user to be able to view the autosorted data prior to closing the workbook.


----------



## Corday (Mar 3, 2010)

Here's the easy tutorial you requested: Excel VBA Tutorial - Easy Excel Macros


----------



## floyd1987 (Dec 22, 2014)

Ah! Thank you very much!


----------



## floyd1987 (Dec 22, 2014)

I tried renaming the Macro as you suggested. It didn't seem to work...

I ended up figuring out a better solution, though. Since "Raw Inventory" is always updated manually (while the "Finished Inventory" columns on this particular worksheet is not updated manually, each cell has formulas to draw the information from the many other sheets that store that data) I changed the Macro to perform the sort function every time the "Raw Inventory" columns are updated.

The problem is, however, that it "flashes" that it sorts everytime the "Raw Inventory" columns are updated, but then immediately reverts back to the original unsorted state.

The code that I'm using is as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Application.Intersect(Worksheets("Finished Inventory").Range("D3:F30"), Target) Is Nothing) Then
DoSort
End If
End Sub

Private Sub DoSort()
Worksheets("Finished Inventory").Range("A2:C368").Sort Key1:=Worksheets("Finished Inventory").Range("A3"), Order1:=xlDescending, Header:=x1Yes
End Sub


----------



## Corday (Mar 3, 2010)

It seems most folks have the problem that they need their original order back. See if this thread is of any help.I know it's not the answer: http://www.techsupportforum.com/forums/f57/can-someone-help-me-auto-sort-in-excel-252123.html


----------



## floyd1987 (Dec 22, 2014)

I glanced over the thread. I'll try to revise the code that I used to make it more like some of the code provided in the examples. I doubt that'll work, though...

Is there a way to code "Make Changes Permanent" or something? (You mentioned that most people seem to have the problem of wanting the original order back, which suggests to me that Excel has a "Revert To Original Order" method that runs, in order to deal with that problem.)

I haven't looked over the Tutorial documentation you sent me. I will do that shortly. Is it setup similar to Oracle's documentation on Java? (Edit: More correctly, do you know of any Excel Macro documentation that's setup similar to Oracle's Java documentation?) If so, then perfect, since I'm already used to that (Java) and know how to "navigate" it better.


----------



## Corday (Mar 3, 2010)

The only thing that comes close is in book form. The "famous" Visual Basic for Dummies. Don't laugh. The books are really pretty good.


----------



## BobJordanB (May 5, 2008)

Corday said:


> It seems most folks have the problem that they need their original order back. See if this thread is of any help.I know it's not the answer: http://www.techsupportforum.com/forums/f57/can-someone-help-me-auto-sort-in-excel-252123.html


I recommend that you bypass vba in this case and use Excel code directly.
Unless your database is really immense it will be adequately fast.

So have a sheet for your original data entries in strict chronological order.
Always add new stuff at the bottom.
On a separate sheet you then calculate the required order and sort the data into that order for usage and printing etc,

I first assume that you are sorting on a single key in one column and that it is unique. I will discuss later how to handle multiple sort keys and non-unique situations.

Let the raw data be on Sheet 'Data' with the purely numeric key in column A and row 1 holding the column names

Let the sorted result be on sheet 'Result' and assume that the data is in the same format as on the data sheet but shifted two columns to the right and two rows down to allow some working areas.

In cell A2 compute the number of rows of raw data on the data sheet
=count(Data!A:A)

In cell A3 type Number

In cell B3 type Order

In cell C3 type = Data!A1

Drag this across to fill out the headings

In cell A4:Awhatever type the sequence 1,2,3,4 etc

In cell B4 type 
=MATCH(SMALL(OFFSET(Data!$A$2,0,0,Result!$A$2),A4),OFFSET(Data!$A$2,0,0,Result!$A$2),0)

This finds the order by looking for the position of the Nth smallest sort key

In cell C4 type 
=OFFSET(Data!A$1,$B4,0)

Now propagate that formula down far enough and across far enough to recreate your sorted data.

So my Data sheet looks like this:
code	data
1	A
3	C
2	B

My Result sheet looks like this:

3 
Number Order code data
1 1 1 A
2 3 2 B
3 2 3 C

If I add the row 
1.5 Fred

The result sheet looks like this


4 
Number	Order	code	data
1	1	1	A
2	4	1.5	Fred
3	3	2	B
4	2	3	C

Now for multiple sort you have to create a hash algorithm to make the multiple columns sort orders code into a single number. That is data dependent. Look up wikipedia etc.

If the codes might be non-unique then you might need to add something to each code to ensure it is unique. You could add the row number over 1000000 for example. That will put duplicated index numbers into adjacent cells and in the original order.

Now tidy up the code to ensure that it is always long enough for your data.

Its an alternative way of thinking

Good luck

Bob J.

PS the attached code has been extended to handle up to 400 entries


----------



## fernand (May 29, 2009)

Dear friend:
From your description I understand that the macro runs perfectly but you want it to activate automatically as you open the workbook.
-In the book that carries the code include 2 cells. Format both of them to indicate date and time. One of the cells should contain formula as follows:
=now() or =ahora() if in spanish
-Say for instance that cell C4 is where you introduce the formula. Once you hit enter, the volatil function calculates itself and will display date and time.
-Now copy this value to the other cell (assume it is C5) that has equal format. Remember to copy only value.
-Save 
-Open visual basic and look for icon corresponding to workbook in your project.
-Establish an automatic routine that will trigger as the book is opened, for example:

Private Sub Workbook_Open()
If range("C5")<>range("C4") then
range("C4").copy
range("C5").select
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
(introduce here the name of the sorting routine you have that works ok)
end if
End Sub

I do not know how your routine ends but I would recomend that the macro ends in the proper page (where C4 and C5 are). The macro will run and update your info. Whether you save workbook or not, the internal clock of your computer will keep on running so the value in cell C4 will continue changing whereas C5 will remain at lower value. 
Therefore, every time you open the book, sorting routine will run with no questions or further actions.
Hope i t works.


----------



## sanjibdutta (Sep 15, 2014)

Corday said:


> Rename the Macro you created and ran Autpen. This will sort on opening.


thanx...


----------

