how do I automatically sort a column on entry

[Guest]

Hi, I have a spreadsheet with a number of entries over 25 columns on sheet1
that are totalled in row 30 (cells A30 to Y30). On sheet2 I have a summary
for each category that is linked to the sums on sheet1. I would like the
sheet2 summary to automatically sort every time I update the numbers on
sheet1. Could anyone help please.
Thanks.

 

[Guest]

You will need a worksheet event to invoke the sort when selected columns are
updated. Record the desired sort as a macro and insert this code in the
worksheet event code.

See the Chip Pearson's site below for more on events:

http://www.cpearson.com/excel/events.htm

 

[Guest]

Thanks, what worksheet event do I use and how do I action it?
I have recorded the sort macro but don't understand how to make it work when
I enter an item. In fact the best way to action the sort is when the relevant
worksheet is opened. Does it need to be pasted onto the worksheet code and if
so what would be the extra line I need to add to the macro to make it run on
selecting that worksheet?
Thanks for your time.

 

[Guest]

This example checks if changes has occurred in columns L & N and if yes, call
the routine SortTable.

You can add your sort macro code tto replace the SortTtable

Right click on the w/sheet tab, "view code" and copy/paste the code below;
modify as required.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isectrng As Range

On Error GoTo Wsexit
Application.EnableEvents = False
Application.ScreenUpdating = False

Set isectrng = Range("L:L,N:N")
Set isect = Application.Intersect(Target, isectrng)
If isect Is Nothing Then
Else
SortTable '<=== insert you code here and remove this line
End If

Wsexit:
Application.EnableEvents = True
End Sub

HTH

 

[Guest]

Hi, that's great, thank you very much.
However, because I enter the data on a different sheet (results) it doesn't
work unless I change something on the sheet with the table (Standings). I
have tried pasting the formula on the input sheet and amending the formula as
below but it isn't working. Any ideas?

NB this module is on the results sheet (where I do the entry) and the table
is on the standings sheet

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isectrng As Range

On Error GoTo Wsexit
Application.EnableEvents = False
Application.ScreenUpdating = False

Set isectrng = Range("e:f") <===== (the range where the data entry occurs)
Set isect = Application.Intersect(Target, isectrng)
If isect Is Nothing Then
Else
Sheets("standings").Select <====(go to the standngs sheet)
Range("B1").Select
Range("A1:B8").Sort Key1:=Range("B2"), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheets("results").Select <====(go back to the results sheet)
End If

Wsexit:
Application.EnableEvents = True
End Sub

 

[Guest]

Michael,
Put your sort code into a separate module (as per your
original macro) and call it from the w/sheet change module as shown below.


Sub mysort()
Sheets("standings").Activate ' <====(go to the standngs sheet)
Range("B1").Select
Range("A1:B8").sort Key1:=Range("B2"), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheets("results").Selec
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isectrng As Range

On Error GoTo Wsexit
Application.EnableEvents = False
Application.ScreenUpdating = False

Set isectrng = Range("e:f") ' <===== (the range where the data entry
occurs)
Set isect = Application.Intersect(Target, isectrng)
If isect Is Nothing Then
Else
Call mysort ' Call sort routine
End If

Wsexit:
Application.EnableEvents = True
End Sub