Chapter 27: Sorting Worksheets into Alphabetical Order

Overview

As workbooks are developed, the number of worksheets rises, and they often end up in a haphazard order. Of course you can drag sheets about using the sheet tabs, but this can be a complex process when there are many sheets. This chapter deals with code that can quickly and efficiently sort your worksheets by name on the tabs at the bottom. If they have alpha names , they will be sorted alphabetically , and if they have numeric names, they will be sorted numerically . Numeric names are sorted before alpha names.

Insert the following code into a module:

 Sub sortsheet() 
 For i = 1 To Sheets.Count 
 
 For j = 1 To Sheets.Count - 1 
 
 If UCase$(Sheets(j).Name) > UCase$(Sheets(j + 1).Name) Then 
 
 Sheets(j).Move after:=Sheets(j + 1) 
 
 End If 
 Next j 
 Next i 
 End Sub 

This code is only designed to work on the current workbook ‚ that is, the one that has focus.

The code cycles through the Sheets collection by using the Count property and a For..Next loop based on that count. It then has a nested loop that cycles through the same sheets less the final one. This is because the code looks at the current sheet and the next sheet in index order. On the final sheet, there is no next sheet, so this would cause an error in the code. The code tests to see if the name of the current sheet in the For..Next loop is greater than the next sheet.

First, the function Ucase is used to convert names into uppercase text because differences between upper- and lowercase could give incorrect results. If the name is greater than the next sheet, then the Move method is used to place it after the next sheet (if you are sorting in ascending order, it should be after the next sheet) by setting the after parameter to the next indexed sheet. Worksheets within a workbook cannot have the same name, so the code does not have to deal with this problem. In this way a ‚“bubble sort ‚½ takes place, and the sheets are sorted into order.

Rename your worksheets by right-clicking the Sheet tab at the bottom and selecting Rename. Use random names and ensure that the order of sheet names is mixed up. Run the code, and the sheets will be transferred into the correct order, together with their contents.