Automatic sort macro
 

Hi all
E3:F8 represent the results of soccer matches. G3:O6 are the resulting
league positions of the 4 teams in the league. I want the league positions to
be updated automatically as the results are entered. Therefore the sort
should be on column N firstly (points scored) then column O which is the goal
difference then on column L which is goals scored. I recorded a macro which
did this and then copied & pasted the code into a Calculate event macro. The
recorded macro version works fine but the Calculate event produces a runtime
error and reports €œMethod €˜Apply of object €˜Sort failed. Can you tell me
what I need to amend in order to get this to work correctly. My code is as
below and I would like to thank you for any help you can give.

Private Sub Worksheet_Calculate()

Range("G3:O6").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add
Key:=Range("N3:N6"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add
Key:=Range("O3:O6"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add
Key:=Range("L3:L6"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("G3:O6")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Al

Automatic sort macro
 

I'm not terribly familiar with 2007 sorting macros but
Try in a worksheet_change event that restricts to the last column where you
enter the data before the sort.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"gramps" wrote in message
...
Hi all
E3:F8 represent the results of soccer matches. G3:O6 are the resulting
league positions of the 4 teams in the league. I want the league positions
to
be updated automatically as the results are entered. Therefore the sort
should be on column N firstly (points scored) then column O which is the
goal
difference then on column L which is goals scored. I recorded a macro
which
did this and then copied & pasted the code into a Calculate event macro.
The
recorded macro version works fine but the Calculate event produces a
runtime
error and reports €œMethod €˜Apply of object €˜Sort failed. Can you tell
me
what I need to amend in order to get this to work correctly. My code is as
below and I would like to thank you for any help you can give.

Private Sub Worksheet_Calculate()

Range("G3:O6").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add
Key:=Range("N3:N6"), _
SortOn:=xlSortOnValues, Order:=xlDescending,
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add
Key:=Range("O3:O6"), _
SortOn:=xlSortOnValues, Order:=xlDescending,
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add
Key:=Range("L3:L6"), _
SortOn:=xlSortOnValues, Order:=xlDescending,
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("G3:O6")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Al

Automatic sort macro
 

Sorry for delay replying. you puy me on the right lines and now up & running.
Thanks for your help
Al

"Don Guillett" wrote:

I'm not terribly familiar with 2007 sorting macros but
Try in a worksheet_change event that restricts to the last column where you
enter the data before the sort.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"gramps" wrote in message
...
Hi all
E3:F8 represent the results of soccer matches. G3:O6 are the resulting
league positions of the 4 teams in the league. I want the league positions
to
be updated automatically as the results are entered. Therefore the sort
should be on column N firstly (points scored) then column O which is the
goal
difference then on column L which is goals scored. I recorded a macro
which
did this and then copied & pasted the code into a Calculate event macro.
The
recorded macro version works fine but the Calculate event produces a
runtime
error and reports €œMethod €˜Apply of object €˜Sort failed. Can you tell
me
what I need to amend in order to get this to work correctly. My code is as
below and I would like to thank you for any help you can give.

Private Sub Worksheet_Calculate()

Range("G3:O6").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add
Key:=Range("N3:N6"), _
SortOn:=xlSortOnValues, Order:=xlDescending,
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add
Key:=Range("O3:O6"), _
SortOn:=xlSortOnValues, Order:=xlDescending,
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Add
Key:=Range("L3:L6"), _
SortOn:=xlSortOnValues, Order:=xlDescending,
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("G3:O6")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Al


.