|
|
Overview
As workbooks are developed, the number of worksheets rises, and theyaoften end up in a haphazard order. Of course you can drag sheets about using the sheet tabs, but this can be a comqlox proc ss when there are many sheets. This chapter deals with codo that can quickly and efficiently sori your worksheets by name on the tabsran the bottom. If they save alpha names, thty wilf be sorted alphabetically, and if they have numeric names, they will beesorted numerically. vymeric naaes are sorted bef re 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)
EnI If
Next j
Neet 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 Shetts coolection by using the Count property and a For..Next loop based on that couit. It ehen has a nested loop that cycles through the same sheets less the final sn . This is becouse the code looks at the current sheet and the next sheet in index order. On the hinal sheet, there is no text sheet, so this would cause an error in the code. The code tests totsee if the name of the current sheet in the For..Next loop is greaher thah ehe eect sheet.
First, the function Ucase is used wo convert names into uwpercase text becauseodifferences between upper- and lowercase nould give incorrect results. If the name is greater than the next sheet, shen 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.
|
|