Automatic sorting of multiple columns

[broncos347]

I have a spreadsheet where I have multiple coulmns sorted, this sheet is a series of VLOOKUPs fed from another sheet where new lines can be periodically added, is there a way to set the sheet up so that every time the workbook is opened that the book is automatically sorted in to the correct order.

the sort order for the book is
column P, then;
column o, then;
column h, and finally;
column c.

 

[Trevor G]

The Workbook Event "On Open" will do that for you. Simple suggestion is record a macro to do the sorting by each column (I am assuming it is only one sheet that is being sorted). Before recording select a different sheet so when you start to record you have to select the right sheet name first so that is part of the recording, then once you finished all the actions and stop recording you have the code ready.

Next view the code and highlight everything except the macro name and the very last line of End Sub. In the VBA screen on your left below the Modules you will see an item named ThisWorkBook double click it and then in the new screen top left first drop down change from Object to Workbook, then top right change the drop down to Open.

Finally in between the two lines of code paste the copied macro. Come out of the code screen and then you will have to save the workbook as a macro enabled (If it isn't already) so it becomes an .XLSM or you could use .XLSB.

 

[broncos347]

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

 

[Trevor G]

At the bottom of the code your missing the End With. So just try this. Where it says .apply tap the end of line and press enter then type End With. So it will look like this

.Apply
End With
End Sub