One of the most commonly used functions in Excel, sorting is usually performed on a list, table, or range of cells, but you can take things a step further and sort your worksheets as well.
By using some code in an Excel workbook, or an Excel Add-In, you can sort all sheets in the active workbook, making it easier to locate sheets when you have many in your workbook.
Open the workbook for which you want to sort the sheets. Press Alt/Option-F11 to open the Visual Basic Editor, and insert a module by selecting Insert → Module and entering the following code:
Sub SortSheets( ) Dim lCount As Long, lCounted As Long Dim lShtLast As Long Dim lReply As Long lReply = MsgBox("To sort Worksheets ascending, select 'Yes'. " _ & "To sort Worksheets descending select 'No'", vbYesNoCancel, _ "Ozgrid Sheet Sort") If lReply = vbCancel Then Exit Sub lShtLast = Sheets.Count If lReply = vbYes Then 'Sort ascending For lCount = 1 To lShtLast For lCount2 = lCount To lShtLast If UCase(Sheets(lCount2).Name) < UCase(Sheets(lCount).Name) Then Sheets(lCount2).Move Before:=Sheets(lCount) End If Next lCount2 Next lCount Else 'Sort descending For lCount = 1 To lShtLast For lCount2 = lCount To lShtLast If UCase(Sheets(lCount2).Name) > UCase(Sheets(lCount).Name) Then Sheets(lCount2).Move Before:=Sheets(lCount) End If Next lCount2 Next lCount End If End Sub
Close the window to get back to your worksheet and save your workbook.
To run the code, click in any worksheet, press Alt/Option-F8, select the macro, and press Run. You will be asked if you want to sort your sheets in ascending (A - Z) or descending order (Z - A)
In this tutorial:
- Retrieve a Workbook's Name and Path
- Excel's Three-Criteria Limit for Conditional Formatting
- Run Procedures on Protected Worksheets
- Distribute Macros
- Automatically Add Date/Time to a Cell upon Entry
- Create a List of Workbook Hyperlinks
- Find a Number Between Two Numbers
- Name a Workbook with the Text in a Cell
- Sort Worksheets
- Password-Protect a Worksheet from Viewing