Alphabetize Sheet Tabs?

tabcourt

New Member
Joined
Sep 24, 2003
Messages
19
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.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Try this:

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

Code:
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
 
Upvote 0
Wow! That worked perfectly and fast. I had 104 tabs to sort and I would have gone nuts without it. Thanks!
 
Upvote 0
This is why i absolutely love this site. I always find exactly what im looking for. This macro works perfectly, thanks a lot.
 
Upvote 0
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:
 
Upvote 0
worksheet tabs sort

found this VBA code to sort worksheet tabs. can it be made to only sort selected sheets?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top