Quickly rearrange Excel worksheets in alphabetical order using VB for Ms Excel

by Excel On Steroids on August 2, 2012

Are you looking for one click to quickly sort your worksheets in alphabetical order? Instead of dragging and manually arranging the worksheets, by using VB for Ms Excel worksheets can quickly be rearranged in alphabetical order. Bear in mind that Excel has no in built function to sort worksheets. Therefore if you have a workbook with many worksheets the task of rearranging your worksheets has been simplified. Follow our step by step instructions on how that can be done.

� Applies To: Excel 2003, 2007 and 2010

1. � Press ALT + F11 to open VB for Excel

2. From the Menu bar, Select Insert –Module

3. On the right hand side copy & paste the Visual Basic coding below

� Sub Sortsheets()

Dim sCount As Integer, K As Integer, L As Integer

Application.ScreenUpdating = False

sCount = Worksheets.Count

If sCount = 1 Then Exit Sub

For K = 1 To sCount – 1

For L = K + 1 To sCount

If Worksheets(L).Name < Worksheets(K).Name Then

Worksheets (L).Move Before:=Worksheets(K)

End If

4. From the Menu bar, select File, Close and Return to MS Excel

5. Press ALT + F8 to get a list of macros

6. Select Sortsheets

7. Select Run

� N.B

When saving the workbook Save As Macro Enabled Workbook
Ensure that you set the appropriate macro security level
- Select the Office Button
- Select the Excel Options button
- On the left side of the dialogue box, select Trust Centre
- On the right side, select the Trust Center Settings button
- On the left side ,select Macro settings
- Select Disable all macros with notification
- Select OK,OK

If you have any suggestions for an Excel Tips & Tricks topic, or you would like help with a particular function, please email enablement@alchemex.com and yours could be the next Tip of the Week.

Alchemex provides examples of MS Excel procedures for illustration only, without warranty expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The MS Excel procedures on this web site are provided “as is” cannot be guaranteed that they can be used in all situations

by Excel On Steroids

Save time and work smarter every day with Excel on Steroids Tips & Tricks, designed as a continuous learning tool to help you become an Excel ‘power user’. If you would like to receive these informative tips in your inbox each week, please subscribe here.

2 comments… add one

John P. Hart February 7, 2013 8:18 pm
Sorry, but this doesn’t work for me! Returns a compile error( even after adding a missing End Sub )
Error message pop-up: Compile Error For without Next
Sub Sortsheets()
Dim sCount As Integer, K As Integer, L As Integer
Application.ScreenUpdating = False
sCount = Worksheets.Count
If sCount = 1 Then Exit Sub
For K = 1 To sCount – 1
For L = K + 1 To sCount
If Worksheets(L).Name < Worksheets(K).Name Then
Worksheets (L).Move Before:=Worksheets(K)
End If
END Sub
Would love to use this, have use for it monthly!
Thanks
Reply
Paula February 26, 2013 12:14 am
Here is the script:
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
Reply

Quickly rearrange Excel worksheets in alphabetical order using VB for Ms Excel

Further Reading: