MS-Excel / General Formatting

---

Sort Worksheets

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.

The Code

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)