How To Arrange Worksheets In Alphabetical Order Excel

Suppose there are multiple worksheets in your Excel workbook, and you want to arrange the worksheets in alphabetical order by the worksheet tab name, how to do it? This article will tell you the methods of how to arrange Excel worksheets in alphabetical order.

1. Move & Arrange Excel Worksheets By Name In Alphabetical Order Manually.

1. If you have fewer worksheets in your Excel workbook, you can move and arrange them by hand.
2. Just click the worksheet tab with your mouse left key and hold on, then drag the worksheet tab to the place where you want to order it.
3. You can also right-click the worksheet tab, then click the Move or Copy… menu item in the popup menu list to open the Move or Copy dialog window.
4. Then you can select the worksheet where you want to move the current worksheet in the worksheet list.
5. There is an option item with the name (move to end), when you select this item and click the OK button, the worksheet will be moved to the end of the current workbook.

2. How To Arrange Worksheets In Alphabetical Order Excel Programmatically.

1. If there are a lot of worksheets in your Excel workbook, then arranging them manually is not efficient.
2. But we can use VBA to write an Excel macro and order the worksheets using the VBA source code in the macro.
3. We can also add an icon button in the Excel Quick Access Tool to call the macro when you click the button icon.
4. Below is the source code of the Excel macro.

   'This function will order the excel worksheets in ascending or descending order.
   Sub SortExcelWorkSheets()
   
   ' Define the outer loop variable i.
   Dim i As Integer
   
   ' Define the inner loop variable j.
   Dim j As Integer
   
   ' Define a variable to record user selection in the pop up message box.
   Dim iAnswer As VbMsgBoxResult
   
   
      '
      ' Pop up a message box to let user to choose how to sort the worksheets by worksheet name.
      ' If user click the Yes button, it will sort the excel worksheets tab by name in ascending order,
      ' If user click the No button, it wll sort the excel worksheets tab by name in descending order.
      '
      iAnswer = MsgBox("Click Yes to sort the excel worksheets in ascending order, click No in descending order." & Chr(10), _
        vbYesNoCancel + vbQuestion + vbDefaultButton1, "Sort Worksheets")
      
      'Loop in the all worksheets.
      For i = 1 To Sheets.Count
      
         
         For j = 1 To Sheets.Count - 1
   
            '
            ' If user click Yes then sort the worksheets by name in ascending order.
            '
            If iAnswer = vbYes Then
               
               
               ' If the current worksheet name is bigger than the next worksheet name in alphabetical order.
               If UCase$(Sheets(j).Name) > UCase$(Sheets(j + 1).Name) Then
               
                  ' Move the current worksheet after the next worksheet.
                  Sheets(j).Move After:=Sheets(j + 1)
               
               End If
   
       
            '
            ' If the user clicks No button, then sort the worksheets by name in descending order.
            '
            ElseIf iAnswer = vbNo Then
            
               'If the current worksheet name is smaller than the next worksheet name in alphabetical order.
               If UCase$(Sheets(j).Name) < UCase$(Sheets(j + 1).Name) Then
                  
                   ' Move the current worksheet after the next worksheet.
                  Sheets(j).Move After:=Sheets(j + 1)
               
               End If
            
            End If
         
         Next j
      
      Next i
   
   End Sub

5. You can read the article How To Run A Macro Using A Button On The Excel Quick Access Toolbar to learn how to create the Excel macro and how to add an icon on the Excel Quick Access toolbar to call the macro quickly and easily.
6. When you invoke the above macro, it will prompt a message dialog to let the user select the sort order on the Excel worksheets.
7. If the user clicks the Yes button, it will sort the worksheets by name alphabetically in ascending order.
8. If the user clicks the No button, it will sort the worksheets by name alphabetically in descending order.