# Can someone help me Auto Sort in Excel?



## progpeps

Hi everyone,

I was wondering if someone can help me autosort a list of data.

What I have is the schedule for the upcoming European Cup of Football.

so I would like to have the standings auto sorted when people enter their predictions on the sheet...

Worksheet 1 has the predictions and on the right it has a blank table for the teams to go there... (Where I want the sorted data to go)

Worksheet 2 is just a bunch of formulas

worksheet 3 has the standings but not sorted in the table...

I would like it sorted by points first, and if their is a tie then sorted by GD (goal differential)

any help would be great 

Thank a lot guysray:


----------



## David M58

Here are some tips that may help you get started. If, for example, you wanted to sort the "Group A" table on the third worksheet in your Excel file, you would select that worksheet, then select the appropriate range (E3:M7 in this example), go to the Excel menu and select _Data_ and then _Sort_, select the _Header row_ option, sort by Pts descending (or ascending), then by GD descending (or ascending).

If you wanted to perform that same operation using a macro, here's some sample code:


Code:


Sub DoSort()
    Dim ws As Worksheet
    Set ws = Worksheets(3)
    ws.Range("E3:M7").Sort Key1:=ws.Range("M3"), Order1:=xlDescending, _
        Key2:=ws.Range("L3"), Order2:=xlDescending, Header:=xlYes
End Sub

Modify as necessary.


----------



## progpeps

thank you...

is there any way to get it to update without hitting the pay button on the macro? 

unless i'm doing something wrong here...

like how do I get it to autmatically sort without running the macro again?


----------



## David M58

Excel gives you several ways to automatically perform tasks. You could, for example, set it up to do that sorting subroutine every time someone enters data on the first worksheet. Or you can have it automatically perform the sorting right before the workbook is saved. Or you can have it do the sorting when the workbook is first opened. And, of course, you can have the sorting done when someone clicks on a command button, or when someone presses a keystroke combination, such as Ctrl-e.

First, you should decide how you want to handle the sorting. Do you want it to sort when someone types in a number? Or when someone saves the workbook? You have a number of choices.


----------



## progpeps

I would like it to sort when you enter a number in any of the cells E9:F32

say you enter

E F
1(it sorts) 5(it sorts)

Thanks


----------



## David M58

Here's one way to set it up. In Excel, press Alt-F11 to get to the Visual Basic Editor, then Ctrl-R to display the Project Explorer. In the Project Explorer, double-click the object Sheet1 (EC Fixtures) to open the code window. Put in the following code.


Code:


Private Sub Worksheet_Change(ByVal Target As Range)
    If Not (Application.Intersect(Worksheets(1).Range("E9:F32"), Target) Is Nothing) Then
        DoSort
    End If
End Sub

Private Sub DoSort()
    Worksheets(3).Range("E3:M7").Sort Key1:=Worksheets(3).Range("M3"), Order1:=xlDescending, _
        Key2:=Worksheets(3).Range("L3"), Order2:=xlDescending, Header:=xlYes
    Worksheets(3).Range("E10:M14").Sort Key1:=Worksheets(3).Range("M10"), Order1:=xlDescending, _
        Key2:=Worksheets(3).Range("L10"), Order2:=xlDescending, Header:=xlYes
    Worksheets(3).Range("E17:M21").Sort Key1:=Worksheets(3).Range("M17"), Order1:=xlDescending, _
        Key2:=Worksheets(3).Range("L17"), Order2:=xlDescending, Header:=xlYes
    Worksheets(3).Range("E24:M28").Sort Key1:=Worksheets(3).Range("M24"), Order1:=xlDescending, _
        Key2:=Worksheets(3).Range("L24"), Order2:=xlDescending, Header:=xlYes
End Sub

Now, whenever you make a new entry in any cell in the range E9:F32 on the first worksheet, it will perform that sorting subroutine on the third worksheet. Modify as necessary.


----------



## progpeps

thank you so much Dave...

I was trying that other function as well but I was missing the If Not, so it wasn't calling to DoSort function


Thank for you help d00d

!!!!!!


----------



## David M58

Glad to help! :smile:


----------



## KirKanu

Great! This thread was of huge help :tongue: Mine works too!

Thank you big time!

PS: Hmm... Now I have to do all those matches and groups


----------



## sschreib

I tried the code above but got this error: "Run-time error '1004: Application-defined or object-defined error". Any suggestions? Here is the code that worked for me in excel 2003, it will not work either:
Private Sub Worksheet_Calculate()
Range("A1:C141").Sort _
Key1:=Range("B2"), _
Order1:=xlAscending, _
Key2:=Range("A2"), _
Order2:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
End Sub


----------



## criscoduck

okay... I have tried to get this to work, but I cant seem to pull it off. I have a similar setup but apprantely different enough to make the sample code in this thread not work.

All of my information is located on one single worksheet. My data entry range is from E4:G16. This info is plugged into formulas that populate my table located in the range of J2:Q5. I want to have it auto sort as people enter their data and I want it to sort by my Q column (Q1 is my header of the info - Q2 thru Q5 contains the data).

Here is the code I have tried:



> Private Sub Worksheet_Change(ByVal Target As Range)
> If Not (Application.Intersect(Worksheets(1).Range("E3:g16"), Target) Is Nothing) Then
> DoSort
> End If
> End Sub
> 
> Private Sub DoSort()
> Worksheets(1).Range("j2:q5").Sort Key1:=Worksheets(1).Range("q1"), Order1:=xlDescending, Header:=xlYes
> End Sub


----------

