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.
Awesome! Thanks for this