Automatic sort macro

[gramps]

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.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add
Key:=Range("N3:N6"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add
Key:=Range("O3:O6"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.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

 

[Don Guillett]

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.

 

[gramps]

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