Office-it.orG

This is VB code which will auto sort all sheets in the active workbook after execute it. It is easy for us to locate sheets when we have many sheets in our workbook.

Write the Code

Open the workbook for which we want to sort the sheets and press Alt + F11 to open the Visual Basic Editor, and select Insert > Module to insert a module. Copy and paste the following code in module just created.

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, _
"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

Now close your Visual Basic Editor and return to your worksheet and save it.

Running the Code

To do this, press Alt + F8, select the macro named “SortSheets”, and press Run. You will be asked the way you sort your sheets either in ascending (A – Z) or descending order (Z – A).

This VB Code works with all version of Excel.

Popularity: 4% [?]