How to quickly rearrange your worksheets in alphabetical order

Are you looking for a shortcut to quickly rearrange your worksheets in alphabetical order?  Instead of dragging and manually arranging your worksheets, you can use VB for Microsoft® Excel® to quickly rearrange them into alphabetical order.

Excel currently has no built-in function to rearrange your worksheets. Therefore, if you have a workbook with many worksheets, the task of rearranging them may become long and drawn out, but luckily there is a way for this to be simplified. Follow our step by step instructions on how that can be done.

You are welcome to download the workbook to practice.

Applies to: Microsoft^® Excel^® 2013 and 2016.

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 SortWorksheets()

 ‘ sort worksheets in a workbook in ascending order

 Dim sCount As Integer, i As Integer, j As Integer

 Application.ScreenUpdating = False

 sCount = Worksheets.Count

 If sCount = 1 Then Exit Sub

 For i = 1 To sCount – 1

 For j = i + 1 To sCount

 If Worksheets(j).Name < Worksheets(i).Name Then

 Worksheets(j).Move Before:=Worksheets(i)

 End If

 Next j

 Next i

 End Sub

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 File Tab
  • 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

The worksheets will quickly be sorted in ascending order, thus saving you time.