Option Explicit 
 
Sub xlSortSheets_Test() 
     
    Dim strWhich    As String 
    Dim Which       As Integer 
     
    strWhich = InputBox("enter sort direction:  1 = A -> Z; -1 = Z -> A", _ 
    "Demo of xlSortSheets", 1) 
    If strWhich = vbNullString Then Exit Sub 
    If strWhich = "-1" Or strWhich = "1" Then 
        Which = strWhich 
        Call xlSortSheets(Which) 
        Exit Sub 
    End If 
    MsgBox "only values of -1 and 1 are valid" & vbCrLf & _ 
    "no sorting done.", vbOKOnly 
     
End Sub 
 
Sub xlSortSheets(Optional Which As Integer = 1) 
     '
     '****************************************************************************************
     '       Function:   sorts sheets in active workbook alphbetically
     '       Passed Values:
     '           Which   [Optional, Input, Integer]  sorting direction:
     '                    1 ==>  A -> Z
     '                   -1 ==>  Z -> A
     '
     '****************************************************************************************
     '
     
    Dim I               As Integer 
    Dim J               As Integer 
    Dim SheetNames()    As String 
    Dim temp            As String 
     '
     '           store sheet names
     '
    ReDim SheetNames(Sheets.Count) 
    For I = 1 To Sheets.Count 
        SheetNames(I) = Sheets(I).Name 
    Next I 
     '
     '           sort sheet names via simple bubble sort
     '
    For I = 1 To Sheets.Count - 1 
        For J = I + 1 To Sheets.Count 
            If (Which = -1 And SheetNames(I) < SheetNames(J)) _ 
            Or _ 
            (Which = 1 And SheetNames(I) > SheetNames(J)) Then 
                temp = SheetNames(I) 
                SheetNames(I) = SheetNames(J) 
                SheetNames(J) = temp 
            End If 
        Next J 
    Next I 
     '
     '           alphabetize sheets
     '
    temp = Sheets(Sheets.Count).Name 
    For I = Sheets.Count To 1 Step -1 
        Sheets(SheetNames(I)).Select 
        Sheets(SheetNames(I)).Move Before:=Sheets(temp) 
        temp = SheetNames(I) 
    Next I 
     
End Sub