How to Sort Excel Worksheet Tabs in Alphabetical Order

If you are working on a large number of worksheets in your Excel workbook, it may be difficult to find a specific worksheet. Sorting your excel worksheet tabs alphabetically would make it easier to find quickly what you are actually looking for.

Unluckily, sorting worksheet tabs alphabetically is not built-in to MS Excel, but you can create and add a macro to your workbook that will allow you to sort tabs in ascending or descending order. In this Excel Guide, we’ll walk you through the steps to to add a macro available on Microsoft’s support site to your Excel workbook that will sort your worksheet tabs.

To start, press Alt+F11 to open the Microsoft Visual Basic for Applications (VBA) editor. Then, go to Insert > Module.

Copy and paste the following macro from Microsoft into the module window that displays.

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

The VBA editor automatically names each module with a number on the end, such as Module1, Module2, etc. You can simply accept the default name of the module. However, if you plan to add other macros to your workbook, it’s a good idea to rename each module so you know what they are. We’ll rename our module to show you how.

To rename the module, select the text in the Name box for the module under Properties in the left pane.

Type a name for the module in the Name box and press Enter. Note that the module name cannot contain spaces.

The name of the module changes in the Modules list under Project in the left pane.

Close the VBA editor by going to File > Close and Return to Microsoft Excel.

Now, we run the macro to sort our tabs. Press Alt+F8 to access the list of macros on the Macro dialog box. Select the macro in the list (in our case there is only one macro), and click “Run”.

The following dialog box displays, allowing you to choose whether you want to sort your worksheets in ascending or descending order. We want to sort them in ascending order, so we click “Yes”.

The worksheet tabs are now arranged in alphabetical order.

The macro you added is part of your workbook now, but when you save it, you’ll probably see the following dialog box. That’s because you saved your workbook as an .xlsx file, which is a normal Excel workbook format that does not include macros. To include macros in your workbook, and be able to run them, you must save your workbook as a macro-enabled workbook, or an .xlsm file. To do this, click “No” on this dialog box.

The Save As dialog box displays. Navigate to where you want to save the macro-enabled workbook, if you’re not already in that folder. Select “Excel Macro-Enabled Workbook (*.xlsm)” from the “Save as type” drop-down list.

Click “Save”.

If you don’t save the workbook as a macro-enabled workbook (.xlsm file), the macro you added will be deleted. You might want to delete the .xlsx version of your workbook so you don’t forget to use the .xlsm version of your workbook if you want to add more worksheet tabs and sort them again using the macro. You can always save the workbook as an .xlsx file again if you don’t want to use macros anymore.

No comments:

Powered by Blogger.