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 InsertModule.
  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

vba

  1.  From the Menu bar, select File, Close and Return to MS Excel.
  2.  Press ALT + F8 to get a list of macros.
  3.  Select Sortsheets.
  4.  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.

Sage © Sage South Africa Pty Ltd 2016 . All Rights Reserved.