Alphabetize Sheet Tabs?

[tabcourt]

I've got a bunch of worksheets that I'd like to re-arrange in alphabetical order. Anyway to do this quickly? Or do I just have to drag and drop them myself? (WXP, Excel XP). Thanks.

 

[CDICKENS]

Try this:

Enter this code into the VBE and then run the sort sheets macro.

Option Explicit

Sub SortSheets()

'this routine sorts the sheets of the active workbook
'in ascending order

    Dim SheetNames() As String
    Dim SheetHidden() As Boolean
    Dim i As Integer
    Dim SheetCount As Integer
    Dim VisibleWins As Integer
    Dim Item As Object
    Dim OldActive As Object
    
    'exits procedure if no active workbook
    If ActiveWorkbook Is Nothing Then Exit Sub
    SheetCount = ActiveWorkbook.Sheets.Count
    
    'Check for protected workbook structure
    If ActiveWorkbook.ProtectStructure Then
        MsgBox ActiveWorkbook.Name & " is protected.", _
            vbCritical, "Cannot Sort Sheets."
        Exit Sub
    End If
    
    'Disable Ctrl+Break
    Application.EnableCancelKey = xlDisabled
    
    'determine the number of sheets
    SheetCount = ActiveWorkbook.Sheets.Count
    
    'redimension the arrays
    ReDim SheetNames(1 To SheetCount)
    ReDim SheetHidden(1 To SheetCount)
    
    'store a reference to the active sheet
    Set OldActive = ActiveSheet
    
    'fill array with sheet names
    For i = 1 To SheetCount
        SheetNames(i) = ActiveWorkbook.Sheets(i).Name
        'MsgBox SheetNames(i) 'this is to verify work
        'Debug.Print SheetNames(i) 'this is to verify work in window
    Next i

    'fill array with hideen status of sheets
    For i = 1 To SheetCount
        SheetHidden(i) = Not ActiveWorkbook.Sheets(i).Visible
        'unhide hidden sheets
        If SheetHidden(i) Then ActiveWorkbook.Sheets(i).Visible = True
    Next i
    
    'sort the array in ascending order
    Call BubbleSort(SheetNames)
    
    'For i = 1 To SheetCount
    '    Debug.Print SheetNames(i)
    'Next i

    'turn off screen updating
    Application.ScreenUpdating = False

    'move sheets
    For i = 1 To SheetCount
        ActiveWorkbook.Sheets(SheetNames(i)).Move _
            before:=ActiveWorkbook.Sheets(i)
    Next i
    
    'rehide sheets
    For i = 1 To SheetCount
        If SheetHidden(i) Then ActiveWorkbook.Sheets(i).Visible = False
    Next i
    
    'reactivate the original active sheet
    OldActive.Activate
    
End Sub

Sub BubbleSort(List() As String)

    'Sorts the List array in ascending order
    
    Dim First As Integer
    Dim Last As Integer
    Dim i As Integer
    Dim j As Integer
    Dim Temp As String
    
    First = LBound(List)
    Last = UBound(List)
    For i = First To Last - 1
        For j = i + 1 To Last
            If UCase(List(i)) > UCase(List(j)) Then
                Temp = List(j)
                List(j) = List(i)
                List(i) = Temp
            End If
        Next j
    Next i
    
End Sub

 

[AustinATL]

Wow! That worked perfectly and fast. I had 104 tabs to sort and I would have gone nuts without it. Thanks!

 

[CDICKENS]

Great glad I could help.

 

[kristinnj]

This is why i absolutely love this site. I always find exactly what im looking for. This macro works perfectly, thanks a lot.

 

[CDICKENS]

This is why i absolutely love this site. I always find exactly what im looking for. This macro works perfectly, thanks a lot.

I am glad that was helpful! Even 1 year later! :wink:

 

[UPN]

worksheet tabs sort

found this VBA code to sort worksheet tabs. can it be made to only sort selected sheets?

 

[larrydunn]

In regard to sorting only selected sheets, you might be interested in looking at my shareware utilities, at excelutilities.com. They give you the ability to specify the sequence of all or any sheets. You simply enter the tab names in a column (there's a utility to do that for you, too!) and sort the names into any sequence desired. Then Utilities - Workbook - Tab Sequence allows you to make the tab sequence follow the order in the list.

 

[CDICKENS]

Re: worksheet tabs sort

found this VBA code to sort worksheet tabs. can it be made to only sort selected sheets?

The answer would be yes.

It would be relatively simple to modify the code to check to see if multiple sheets were selected and if so only sort those. BUt woudl you want them sorted in their current place, or sorted to the front, or sorted to the back of the workbook?

Chuck

 

[UPN]

sorted to front

I would want them sorted to the front. I have 5 sheets, one of them is a master, which all other news sheets are created from. I they are put before all the other sheets when created, but would like to only sort the new sheets.

I don't know whatto change to do this