Site icon ExcelNotes

How to Sort Worksheets Alphanumerically in a Workbook

When working with a large database that contains many worksheets, it will be helpful to sort worksheets alphanumerically. Unfortunately, Excel 2016 does not provide a direct way to sort worksheets. The following is a macro to perform the task.

Step 1: Click anywhere in the workbook, then press "ALT + F11" key to open the "Microsoft Visual Basic for Applications" window;

Step 2: Click Insert, then click Module in the drop down list;

Step 3: In the new Module window, copy and paste the following codes or refer to the Microsoft Support page;

Sub Sort_Active_Book()
Dim i As Integer
Dim j As Integer
Dim iAnswer As VbMsgBoxResult
'
' Prompt the user as which _
direction they wish to
' sort the worksheets.
'
iAnswer = _
MsgBox("Sort in Ascending?" _
& Chr(10) _
& "No will sort Descending ", _
 vbYesNoCancel + vbQuestion _
  + vbDefaultButton1, _
  "Sort Worksheets")
  For i = 1 To Sheets.Count
  For j = 1 To Sheets.Count - 1
'
' If the answer is Yes, then _
sort in ascending order.
'
  If iAnswer = vbYes Then
  If UCase$(Sheets(j).Name) _
   > UCase$(Sheets(j + 1) _
   .Name) Then
   Sheets(j).Move _
   After:=Sheets(j + 1)
   End If
'
' If the answer is No, then _
sort in descending order.
'
  ElseIf iAnswer = vbNo Then
  If UCase$(Sheets(j) _
  .Name) < UCase$ _
  (Sheets(j + 1).Name) Then
  Sheets(j).Move _
  After:=Sheets(j + 1)
            End If
         End If
      Next j
   Next i
End Sub

Step 4: In the "Microsoft Visual Basic for Applications" window, click "Run" button;

Step 5: In the "Sort Worksheets" window, click "Yes" if sort sheets in Ascending Order; or click "No" if sort worksheets in Descending Order.

Step 6: For example, click "Yes" to sort worksheets in ascending order.

Exit mobile version