Sorting table automatically

[McG]

I have a set of results which are collected into a table B3:AB23.
The data is then sorted by the following macro below.
I would like the table to be automatically sorted without me having to
use the Keyboard Shortcut: Ctrl+y 30-50 times a day.
Can this be done?



Sub League()
'
'
' Keyboard Shortcut: Ctrl+y
'
Range("B3:AB23").Select
Selection.Sort Key1:=Range("C4"), Order1:=xlDescending,
Key2:=Range("J4") _
, Order2:=xlDescending, Key3:=Range("H4"),
Order3:=xlDescending, Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
End Sub

 

[Mark E. Philpot]

Go to the vb Editor, Click on the worksheet to reveal its
underlying module. Where the Dropdown says "General",
Click the arrow to show "WorkSheet". The Dropdown next to
that said "Declarations" now says SelectionChange.
Click the SelectionChange to create a sub. Put the League
macro here.
Each time the worksheet changes in the selection range,
the macro will be initiated. There will be no reason to
goto that worksheet again to make changes or updates.

with regards
Mark E. Philpot

 

[Frank Kabel]

Hi
I would put this kind of macro in the worksheet_change event so it's
gets only executed if new data is added (but this depends on the OP's
requirements)

 

[McG]

On Thu, 20 May 2004 21:32:02 -0700, "Mark E. Philpot"
Mark I am not having any luck with the simple instructions that you
have sent. When I go to Module 1 in this case, only the General tab is
available.

(Desperate mode on "If I do get to the sheet3 Sheets("League Table")
via vb editor and paste the following code Private Sub
Worksheet_SelectionChange(ByVal Target As Excel.Range)
'
' League Macro
' Macro recorded 21/05/2004 by BT
'

'
Sheets("League Table").Select
Range("B4:AB24").Select
Selection.Sort Key1:=Range("C5"), Order1:=xlDescending,
Key2:=Range("J5") _
, Order2:=xlDescending, Key3:=Range("H5"),
Order3:=xlDescending, Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
Range("A4").Select
End Sub


All get is a continous loop.

HELP

 

[Frank Kabel]

Hi
try the following:
- open your workbook
- in the workbook right-click on the sheet name
- choose 'Code'

Now the VBA editor with the worksheet module should appear. Follow the
instructions given in the previous post

 

[McG]

Hi
try the following:
- open your workbook
- in the workbook right-click on the sheet name
- choose 'Code'

Now the VBA editor with the worksheet module should appear. Follow the
instructions given in the previous post

No joy. I can see the module but I cannot select "worksheet" from the
dropdown list, it only stays on (General) On the other dropdown is
the (Macro name) and (Destinations) are available. Can I email the
sheet to you?

 

[Frank Kabel]

Hi
go ahead emailing your file
email: frank[dot]kabel[at]freenet[dot]de

 

[McG]

Hi
go ahead emailing your file

Any joy Frank?

 

[Frank Kabel]

Hi
haven't receive a file. Sorry

 

[McG]

Hi
haven't receive a file. Sorry

Frank I just sent the file.

 

[Frank Kabel]

Hi
sheet is on it's way back

 

[McG]

Hi
sheet is on it's way back

I would like to thank you sorting this problem out. Mark's
instructions were valid but I could not get to grips with modules as
it was a hit and miss for me.

 

[Jamie]

I am having a similar problem. I want a table to automatically update
when any of the fields change. The table is very small. All I want to
do is for the row with the largest answer to column F to be at the top
and the smallest answer to be at the bottom. I was wonderin what I
needed to do?

I tried to follow the instructions that Mark gave but the macro came
up with errors.

Please advice.

Jamie

 

[Dave Peterson]

Your post appears to be an orphan to me.

But I'd suggest you record a macro when you do it manually. Then tweak it to
make sure it includes all the rows you need (if you add more/delete existing).

Then rightclick on the worksheet tab that should have this behavior. Select
View code and paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRng As Range

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("f:f")) Is Nothing Then Exit Sub

On Error GoTo errHandler:
Application.EnableEvents = False
With Me
Set myRng = .Range("a1:H" & .Cells(.Rows.Count, "A").End(xlUp).Row)
myRng.Sort Key1:=.Range("f2"), Order1:=xlDescending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False
End With

errHandler:
Application.EnableEvents = True

End Sub

Notice that I assumed your range was in A:H and the last row of the range could
be determined by column A.

Modify to match your data.

===
Personally, this would drive me nuts! I'd want to see my change where I put
it--just in case I made a typo. I'd use an equivalent macro that I could run on
demand.

This would go in a general module:

Option Explicit
Sub SortMeNow()

Dim myRng As Range

With ActiveSheet
Set myRng = .Range("a1:H" & .Cells(.Rows.Count, "A").End(xlUp).Row)
myRng.Sort Key1:=.Range("f2"), Order1:=xlDescending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False
End With

End Sub

All the stuff about determining the range would still apply.