Automatic sort of table into rank order when a new record is added

[tbones]

Hi all, I have been using a table that I input figures onto. What I want to be able to do is to have the rows move in order of rank automatically as new data is inputted.
So say I have 10 columns and say 10 rows and I want to add data to these rows I then want the table to sort in order of largest to smallest of say column 8 .

I have been using a VBA code that will do this if I double click on the header row however I dont want to have to do this and I want it to move the rows in order once the data has been inputted.

Here is the VBA I use so that I can click on the header to sort.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim KeyRange As Range
Dim ColumnCount As Integer
ColumnCount = Range("A1:FE25").Columns.Count
Cancel = False
If Target.Row = 1 And Target.Column <= ColumnCount Then
Cancel = True
Set KeyRange = Range(Target.Address)
Range("A1:FE25").Sort Key1:=KeyRange, Header:=xlYes
End If
End Sub

Any help to be able to do the same as the code but without having to double click on the header column to sort would be appreciated.

 

[tbones]

Thank you for providing the actual file - this changes things totally
Because your column FC changes as a result of a formula, a Worksheet Change event will not work at all - the code needs to be triggered by a worksheet calculate event instead. The drawback is that the sheet will sort whenever a calculation occurs anywhere on the sheet - you can't specify which cells change via a calculation to trigger the code.
Here is the new code:

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
    With Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, Cells(1, Columns.Count).End(xlToLeft).Column))
        .Sort Key1:=Range("FC1"), order1:=xlDescending, Header:=xlYes
    End With
    Application.EnableEvents = True
End Sub

and here is the link to your workbook with the code already added: football predictor project 4.xlsm

Hi Again, where I have moved things about I am now unable to get the rows to move automatically according to how large the value of column H is ranked largest to smallest I have tried the code you gave me and changed the range column but alas I am unable to get it to sort automatically.

Any ideas as to what i am doing wrong please.
Here is a link to a dummy file that I am using to make it work

Dummy File

 

[kevin9999]

where I have moved things about I am now unable to get the rows to move automatically according to how large the value of column H

If you move the data around, you need to change the sort key to whatever column you're now using for the sort. It used to be FC - now it's H. Like this:

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
    With Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, Cells(1, Columns.Count).End(xlToLeft).Column))
        .Sort Key1:=Range("H1"), order1:=xlDescending, Header:=xlYes
    End With
    Application.EnableEvents = True
End Sub

 

[tbones]

If you move the data around, you need to change the sort key to whatever column you're now using for the sort. It used to be FC - now it's H. Like this:

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
    With Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, Cells(1, Columns.Count).End(xlToLeft).Column))
        .Sort Key1:=Range("H1"), order1:=xlDescending, Header:=xlYes
    End With
    Application.EnableEvents = True
End Sub

Thank you I got that to work on the dummy file however when I add it to the main file and I change the range value it does not seem to work. Would that be it is because I am linking the sheet to other sheets/workbooks?

 

[kevin9999]

I have no idea why it doesn't work for you. As you said, it works on the file you shared with me. Unless you share the actual file you're using it on then I have no hope of finding the cause of the problem.