Sorting worksheet tabs alphabetically in Excel

Forum to talk about all the latest technologies - ins and outs!!
Post Reply
Admin
Site Admin
Posts: 325
Joined: Tue Sep 25, 2007 12:41 pm

Sorting worksheet tabs alphabetically in Excel

Post by Admin »

It's very simple regardless of how large your excel workbook is.


Image


You just follow the steps assuming that your excel file is open:

Click ALT+F11 to open visual basic editor.

When the VB Editor is open, click Insert > Module

Copy and paste the following code inside the module.


#############################

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

#############################


Code source: Microsoft


Save the module with any name for your comfortability, like myexcelsorting (no spacing).


Image


Now press F5 to run the macro. Click 'Run' on the open 'Macro' window.


A new small window 'Sort Worksheets' will open asking whether to sort sheets in ascending order, Click 'Yes'. You will see sheets are arranged in ascending order.


Image


That's it.

By the way, you can close your VB Editor now.

Cheers :-)
Post Reply