1. Open Excel and create some sheets in the workbook. Rename them and arrange them randomly (I'm assuming an example sheet to start with). I've named the sheets as navy call list.
2. Now right click on any sheets and click "View Code" as shown in the screenshot below.
3. Now "Microsoft Visual Basic for Applications" window will be opened. Click on "Insert" and click on "Module".
4. A new module will be created and a blank editor will appear. Copy and paste the following code in the editor:
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 Sheets in Ascending Order?" & Chr(10) _
& "Clicking No will sort in Descending Order", _
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
4. Now click on "File" and "Close and return to Microsoft excel".
5. Now in Excel, click on "View > Macros > View Macros". It will open the saved macros.
6. In the Macro window, you should see "Sort_Active_Book" macro. Click on it and click "Run". It will open a prompt, click on Yes if you want to sort the sheet in ascending order, click NO if you want to sort the sheet in descending order.
7. That's it, you can see that the sheets in your workbook are sorted.
Keep visiting for more tips and tricks.
