Auto Sort

  • Thread starter Thread starter cassy01
  • Start date Start date
C

cassy01

I was wandering if it is possible to sort a group automatically so tha
every time the data changes it automatically sorts by a certai
column?

I want to auto sort a table (A2:H12) by column H is this possible ??

Many Thanks
Ben
 
Benn,

On the sheet that you want this to happen....
Right click on the sheet tab and select "View Code"
Then copy and paste the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("H2:H12")) Is Nothing Then
Range("A2").Select
Selection.Sort Key1:=Range("H2"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End Sub

John
 
thanks for your help but it doesnt seem to work !

I right clicked on the tab and click view source and then pasted th
above formula onto it, I then when back and tried to see if it worke
but they just stayed in the same place,

Do you know why ???

I Can Send You The Form if you want.

Many Thanks
Ben
 
Benn,

What I gave you should have worked.
I right clicked on the tab and click view source
Click to expand...
Your choice should have been "View Code".

Yes, you can send me the workbook directly and I'll take a look at it.

John
 
where can i send the file to ??

if you want you can send me a email to (e-mail address removed)

then i will send it to you.

Many Thanks
Ben
 
Benn,

Rec'd your file, got it to work and am sending it back
to you directly.

What you were trying to do was to use the Worksheet_Change
Event on one sheet to sort a table on another sheet.

The following code was placed on your "Scores" sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B5:W15")) Is Nothing Then
Sort_12A
End If
If Not Intersect(Target, Range("B21:Y32")) Is Nothing Then
Sort_12B
End If
If Not Intersect(Target, Range("B38:O44")) Is Nothing Then
Sort_11A
End If
If Not Intersect(Target, Range("B50:S58")) Is Nothing Then
Sort_11B
End If
End Sub

The range addresses above correspond to the ranges that you're
making changes in. When a change is made within one of those
ranges, the associated sub (Sort_12A, Sort_12B, etc.) will be
run.

Those subs appear in a regular module. I probably could have made
this more concise, but it'll work. If you change any of these ranges,
you'll have to change the range references in the code.
Here's the code in those modules:

Option Explicit
Sub Sort_12A()
Application.ScreenUpdating = False
Worksheets("Tables").Activate
With Worksheets("Tables")
.Range("A3:H12").Sort Key1:=Range("H3"), _
Order1:=xlDescending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
End With
Worksheets("Scores").Activate
Application.ScreenUpdating = True
End Sub
Sub Sort_12B()
Application.ScreenUpdating = False
Worksheets("Tables").Activate
With Worksheets("Tables")
.Range("A16:H27").Sort Key1:=Range("H16"), _
Order1:=xlDescending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
End With
Worksheets("Scores").Activate
Application.ScreenUpdating = True
End Sub
Sub Sort_11A()
Application.ScreenUpdating = False
Worksheets("Tables").Activate
With Worksheets("Tables")
.Range("A31:H37").Sort Key1:=Range("H31"), _
Order1:=xlDescending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
End With
Worksheets("Scores").Activate
Application.ScreenUpdating = True
End Sub
Sub Sort_11B()
Application.ScreenUpdating = False
Worksheets("Tables").Activate
With Worksheets("Tables")
.Range("A41:H49").Sort Key1:=Range("H41"), _
Order1:=xlDescending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
End With
Worksheets("Scores").Activate
Application.ScreenUpdating = True
End Sub

Any questions or problems, post back to the ng.

John
 
it doesnt seem to work still !? I Input my data on sheet 1 then click on
sheet 2(tables) look at the tables and they haven't changed places.

Many Thanks
Benn
 
Benn,

What I gave you back was already sorted so you won't see
a change unless you change something that will change
the odrer.

I tested what I gave you and it does work.

Do you have your security level set to "High"???
Set it to "Low" or "Medium" (and if prompted, choose
"Enable Macros")
Tools/Macro/Security

John
 
Benn,

Assuming that you have macros enabled.................
As I said, I already tested and sorted tthe workbook via that code
to see if it worked (and it did).
Maybe what you're changing is not altering the sort???
Try manually sorting them in Ascending order and then
go back and make a change (any change) to the data
on your "Scores" sheet. Any change will prompt a sort and
you should see the data in the "Tables" page change
to "Descending"

John
 
I am lost by what you have posted here. I have a similar request but th
table that I need sorted on a worksheet by itself.

Could you please tell me how to go about just auto sorting a range o
cells like (B2:B5) and expand the selection to include (A2:A5)?

If you would like I can send you the file to look at it.

I really need this to work for a Children's Game show that we put o
here in Las Vegas.

I have also created a link to a powerpoint file to include the tabl
from this excel file. Is there any way to have it auto update
 
Back
Top