Sort the worksheets in a workbook

 1999-12-20    Workbooks    0    79

The procedure below will sort all the worksheets in a workbook in ascending order:

SortWorksheets ActiveWorkbook
SortWorksheets Workbook("MyWorkBookName")

Sub SortWorksheets(wb As Workbook)
' sort worksheets in a workbook in ascending order
Dim sCount As Integer, i As Integer, j As Integer
    If wb Is Nothing Then Exit Sub ' no workbook input
    Application.ScreenUpdating = False
    sCount = wb.Worksheets.Count
    If sCount = 1 Then Exit Sub
    With wb
        For i = 1 To sCount - 1
            For j = i + 1 To sCount
                If .Worksheets(j).Name < .Worksheets(i).Name Then
                    .Worksheets(j).Move Before:=.Worksheets(i)
                End If
            Next j
        Next i
    End With
End Sub
If you want to sort all the sheets in the workbook, replace worksheets with sheets.

Leave a comment:

Your comment will only be published after it has been moderated and found spam free.
Your e-mail address will only be used to display your Gravatar.