Excel VBA Sort Any worksheet by Name with a Named Range and Sort list from any Worksheet returning control to the calling sheet: Code included

 I use Vlookup in several sheets to obtain information in a Master List however, the Master list could have been used to lookup information and having been sorted is no longer in the order needed for Vlookup to work.  So, I needed a quickway to Sort that list into the correct order.  This routine allows me to make sure it is sorted for a lookup from any other sheet,

It also allows one to sort any Named Range, in any sheet by any sort list.

I got tired of needing to access data from a worksheet in an ordered fashion and creating a sort routine for that sheet.  With this, I can create a button, and sort any Range in any way I want from any sheet that needs it. 

The only requirements are:

The sheet must have a Named Range to be sorted and a Named Range which is the list to sort it by.  When calling from a different sheet, the routine activates the sheet with the Range to be sorted, sorts it, and returns to the active sheet it was called from. No more jumping back and forth.  

If I create a Sorting routine for a sheet, that looks up data from a Master list, I can first sort the Master List before calling the routine for the sheet that uses it.  No more messed up lookups because the Master List was sorted by another routine.

For safety, the routine also will debug print information that will allow you to confirm the sorting routine is working the way you want.  If you need different search criteria, the code can be added to this routine to give you even more fine control of the list being sorted.  The sort here is a generic one.  Alphabetical by the Range listed.

If this helps, leave me a comment.

Sub MasterSort(WorksheetName As String, RangeToSort As String, ColumnToSortBy As String)

'

' SortName Macro

' Sort from Any Worksheet any Sort with a NamedRange and SortColumn if  needed for reference in a VLookup form another WorksheetName, RangeToSort and ColumnToSortby

' Retains ActiveWorksheet before Call

' Prints in immediate mode activity for confirmation'

Dim OldWorksheetName As String

OldWorksheetName = ActiveSheet.Name

Debug.Print "Current Active calling Worksheet:" & ActiveSheet.Name, " CodeName of Sheet: "; ActiveSheet.CodeName

'Activate sheet to be sorted

    Worksheets(WorksheetName).Activate

    Worksheets(WorksheetName).Select

    Debug.Print "Worksheet being sorted: "; WorksheetName, " CodeName of Sheet: "; ActiveSheet.CodeName, " Named Range being sorted: "; RangeToSort; " and Column of Sort: "; ColumnToSortBy

'Sort activated sheet

    Range(RangeToSort).Select

    ActiveWorkbook.Worksheets(WorksheetName).Sort.SortFields.Clear

    ActiveWorkbook.Worksheets(WorksheetName).Sort.SortFields.Add2 Key:=Range( _

        ColumnToSortBy), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _

        xlSortNormal

    With ActiveWorkbook.Worksheets(WorksheetName).Sort

        .SetRange Range(RangeToSort)

        .Header = xlGuess

        .MatchCase = False

        .Orientation = xlTopToBottom

        .SortMethod = xlPinYin

        .Apply

    End With

        Range("A1").Select 'places the cursor in the top-left cell.

             

 ' When done, reactivate the calling sheet

 

        Worksheets(OldWorksheetName).Activate

        Worksheets(OldWorksheetName).Select

        Debug.Print "Returning control to " & ActiveSheet.Name

End Sub