New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Sort Worksheet Tabs

Free Excel Macros

Sort Worksheet Tabs

Postby Sisyphus » Fri Feb 10, 2012 7:12 am

This procedure will sort the tabs in your workbook in alphabetical order from left to right.
It is designed to have one or several master sheets which must always stay in front and which, therefore, must be excluded from sorting. By setting the constant 'FirstToSort' to 1 all sheets will be included in the sort. Set it to 2 or higher to exclude any number of sheets from being sorted with the others.
Code: Select all
Public Sub SortSheets()

    ' First worksheet to be included in the sorting:
      ' set as required
    Const FirstToSort As Integer = 1
   
    Dim i As Integer
    Dim Done As Boolean
   
    With ThisWorkbook.Worksheets
        Do
            Done = True
            For i = FirstToSort To .Count - 1
                If .Item(i).Name > .Item(i + 1).Name Then
                    .Item(i + 1).Move Before:=.Item(i)
                    Done = False
                End If
            Next i
        Loop Until Done
    End With
End Sub

The preferred location for this procedure is in a normal code module, but if it is the only procedure in your workbook you can also paste it to the 'ThisWorkbook' module.
You can call this procedure from Tools / Macro / Macros - select 'SortSheets' from the list and press 'Run'.
If you call the procedure from another sub in the same code module it will be better to change its declaration from 'Public' to 'Private'.
Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Return to Excel Macros

Who is online

Users browsing this forum: No registered users and 20 guests