I've finally got round to trying this and i have come up again an error message, the message is compile error - expected end with, VBA is really my strong point and i'm not sure how to resolve, can anyone help?
the code is as follow:
Private Sub Workbook_Open()
Sheets("Renewals ListNEW").Select
ActiveWorkbook.Worksheets("Renewals ListNEW").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Renewals ListNEW").AutoFilter.Sort.SortFields.Add2 _
Key:=Range("N2:N1000"), SortOn:=xlSortOnValues, Order:=xlAscending, _
CustomOrder:="PR,NPR", DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Renewals ListNEW").AutoFilter.Sort.SortFields.Add2 _
Key:=Range("O2:O1000"), SortOn:=xlSortOnValues, Order:=xlAscending, _
CustomOrder:="DY,NDY", DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Renewals ListNEW").AutoFilter.Sort.SortFields.Add2 _
Key:=Range("H2:H1000"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Renewals ListNEW").AutoFilter.Sort.SortFields.Add2 _
Key:=Range("C2:C1000"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Renewals ListNEW").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End Sub