How to use VBA Code to sort worksheets based on a pre-sorted named-range

Requirement:

The user has a range containing the worksheet names in my workbook, about 30 sheets. The user wants to allow the other users to sort the sheets in the order they like for printing.

The user has another range containing the worksheet names and an 'order' column, with 1-n numbers. The user would just change the numbers in the sort order, and the code then sorts that range and copies the sorted worksheet names into the 1d range on another page.

However next the user needs code to actually re-order the worksheets according to the sorted range. The user found a function on a lot of sites made by C.Pearson that accepts an array of names, but it gives no subroutine showing how to create the array or invoke the function. I also saw comments that it was a bit convoluted, but can't comment.

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/120624-vba-code-to-sort-worksheets-based-on-a-pre-sorted-named-range

Solution:

Code:

Sub test()
    Dim rng As Range, i As Long, msg As String
    Set rng = Range("WorksheetNames")
    For i = rng.Count To 1 Step -1
        If rng(i).Value <> "" Then
            If IsSheetExists(rng(i).Value) Then
                Sheets(rng(i).Value).Move after:=rng.Parent
            Else
                msg = msg & vbLf & rng(i).Value
            End If
        End If
    Next
    rng.Parent.Select
    If Len(msg) Then MsgBox "Wrong sheet name" & msg
    Set rng = Nothing
End Sub


Function IsSheetExists(txt As String) As Boolean
    On Error Resume Next
    IsSheetExists = Len(Sheets(txt).Name)
    On Error GoTo 0
End Function

Obtained from the OzGrid Help Forum.

Solution provided by jindon.