Automatic sort formula

Please try below code:

Sub SortData()
Application.ScreenUpdating = False
Dim Ws As Worksheet
Dim LR As Long
Dim Rng As Range
Set Ws = Worksheets("Sheet1")
Set Rng = Ws.Range("A1:D12")
Rng.Copy
Ws.Range("A17").PasteSpecial xlPasteValues
Application.CutCopyMode = False

LR = Ws.Range("A" & Rows.Count).End(xlUp).Row

Ws.Sort.SortFields.Clear
    Ws.Sort.SortFields.Add Key:=Range("C17:C" & LR), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With Ws.Sort
        .SetRange Range("A17:D" & LR)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Ws.Range("A17").Select
Application.ScreenUpdating = True
End Sub

Select allOpen in new window

Above code is with copying the data and sorting.

If you just want to sort then try below:

Sub SortData()
Application.ScreenUpdating = False
Dim Ws As Worksheet
Dim LR As Long
Dim Rng As Range
Set Ws = Worksheets("Sheet1")
LR = Ws.Range("A" & Rows.Count).End(xlUp).Row

Ws.Sort.SortFields.Clear
    Ws.Sort.SortFields.Add Key:=Range("C17:C" & LR), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With Ws.Sort
        .SetRange Range("A17:D" & LR)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Ws.Range("A17").Select
Application.ScreenUpdating = True
End Sub

Select allOpen in new window

Great effort can I ask you to look at the attached and see if it can be improved
Cheers
Ian
autosortmacro.xlsx

Ian,

Please try attached just for sort and I noticed there are some blank cells in BI to BP which where copy pasted, but counting those cells.
I added a macro to clean up those cells and sort as per BM.
Let me know the sheet name and range, where you are copying from Master, I will edit the code.
automatic-sort_V2.xlsm

Thanks guys for your help.
Very happy indeed
Ian

You're welcome Ian! Glad I was able to help you.