Sort Worksheets By

NAME

■ y ou can use VBA to sort the order of the worksheets in a workbook based upon the worksheet name. When you first create a new workbook with three worksheets, Excel lists the sheets in order: Sheet1, Sheet2, and Sheet3. But as you add additional sheets the order of the sheets can change dramatically. For example, if your active sheet is Sheet2 and you instruct Excel to add a new sheet, Excel adds it before Sheet2. If your workbook contains three worksheets, Excel adds the new sheet and names it Sheet4 making the order of your sheets Sheet1, Sheet4, Sheet2, Sheet3. Or course, you can easily resolve this by manually renaming or moving the sheets within the workbook.

Alternately, you can create a subroutine that sorts the worksheets so that Excel lists them in alphabetical order. To do this, you must first determine the number of sheets within the workbook using the Count property.

When you know the number of sheets in a workbook, you need to use For Next looping to cycle through the sheets so Excel can compare the names and move a sheet when one name is greater than another. You can accomplish this with nested looping, which is the process of placing one looping statement within another looping statement. The inside loop executes completely and control returns to the outside loop. See Chapter 6 for more information on using For Next looping statements.

Within the second For Next loop you can use an If Then statement to compare the name of a sheet to the currently smallest sheet name. If that name is smaller, it becomes the new smallest name. Remember, Excel does an alphabetical comparison when you deal with strings. Therefore, "apple" is smaller than "bat" even though the word apple has more characters.

SORT WORKSHEETS BY NAME

SORT WORKSHEETS BY NAME

□ Create a new subroutine.

0 Type Dim SheetCount As

Integer, replacing SheetCount with the variable to store number of sheets.

□ Type SheetCount : Sheets.Count.

0 Type SheetName = Sheets(N).Name.

□ Create a new subroutine.

—0 Type Dim SheetName As String, replacing SheetName with the variable for storing the smallest sheet name.

0 Type Dim SheetCount As

Integer, replacing SheetCount with the variable to store number of sheets.

□ Type SheetCount : Sheets.Count.

0 Type SheetName = Sheets(N).Name.

Was this article helpful?

0 0

Post a comment