How to sort data automatically when file is opened

Press Esc to close

Question:

I would like to have my data in excel automatically sort every time I open it to the preference I have pre-set. I would rather this instead of always having to select my data, hit data, sort, and etc. How would I do this?

Answer:

This can be done by using a VBA macro. For example, if you have the following data:

Unsorted Data

 

The following code will run the sorting subroutine when the workbook is opened:

Private Sub Workbook_Open()
    Call SortByRank
End Sub

And this subroutine will enable the autofilter functionality, clear all existing filters and then filter the data on column B:

Sub SortByRank()

    Range("B4").Select
    Selection.AutoFilter
    
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add _
       
Key:=Range("B1:B6"), SortOn:=xlSortOnValues, _
        Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

And the end result will be this:

Data Sorted

Tags: VBA

Enter your email and get everything you need to know about Excel Formulas!

Free Training Includes :

  • 54 Training Videos
  • 90-Second Formula Exercises
  • Detailed Exercise Solutions
  • And more
Home   |   Ask a question   |   Excel Guides   |   Formula Expert Addin   |   FAQs   |   About Us