Can I "sort" Worksheet Tabs in my Workbook

[Tim58]

I have 15 Worksheets in my Excel Workbook and I know I can move the tabs around to put them in a desired order. Is there a way I can sort the tabs alphabetically?

 

[AnotherHiggins]

The easiest way is to download the free ASAP utility addin - available at

http://www.asap-utilities.com.

It's got a ton of useful stuff on it. Quite honestly I don't use it that often, but it's worth keeping around for situations like this. Once installed, all you have to do is go to ASAP Utilities > Sheets > Sort all sheets in alphabetical order.

If you don't want to download anything, then you can use the macro found at

http://www.cpearson.com/excel/sortws.htm.

*Disclaimer: I'm not affiliated with ASAP, just a customer.

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[bbottin]

Try this:

Sub AlphabetizeSheets()
'
' AlphabetizeSheets Macro
' Macro recorded 2/17/2005 by BBottin
'

'
Sheets.Add
ActiveSheet.Move Before:=Sheets(1)
Sheets(1).Select
SheetIndex = 2
Sheets(SheetIndex).Select

On Error Resume Next
While Err = 0
SheetName = ActiveSheet.Name
Sheets(1).Select
Range("a" & SheetIndex & "").Select
ActiveCell = SheetName
SheetIndex = SheetIndex + 1
Sheets(SheetIndex).Select
Wend

Sheets(1).Select
Range("a2").Select
Selection.Sort Key1:=ActiveCell, Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

DataSheet = ActiveSheet.Name
Rank = 1
While ActiveCell <> ""
SortSheet = ActiveCell
Sheets("" & SortSheet & "").Select
ActiveSheet.Move After:=Sheets(Rank)
Sheets("" & DataSheet & "").Select
ActiveCell.Offset(1, 0).Activate
Rank = Rank + 1
Wend
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
End Sub

 

[HughLerwill]

Tim58,

Try thread707-985378

regards Hugh

 

[mscallisto]

And another (I probably got this from this forem in the past)

Sub SortWorksheets()

Dim N As Integer
Dim M As Integer
Dim FirstWSToSort As Integer
Dim LastWSToSort As Integer
Dim SortDescending As Boolean

SortDescending = False

If ActiveWindow.SelectedSheets.Count = 1 Then
    FirstWSToSort = 1
    LastWSToSort = Worksheets.Count
Else
    With ActiveWindow.SelectedSheets
        For N = 2 To .Count
            If .Item(N - 1).Index <> .Item(N).Index - 1 Then
                MsgBox "You cannot sort non-adjacent sheets"
                Exit Sub
            End If
        Next N
        FirstWSToSort = .Item(1).Index
        LastWSToSort = .Item(.Count).Index
     End With
End If

For M = FirstWSToSort To LastWSToSort
    For N = M To LastWSToSort
        If SortDescending = True Then
            If UCase(Worksheets(N).name) > UCase(Worksheets(M).name) Then
                Worksheets(N).Move Before:=Worksheets(M)
            End If
        Else
            If UCase(Worksheets(N).name) < UCase(Worksheets(M).name) Then
               Worksheets(N).Move Before:=Worksheets(M)
            End If
        End If
     Next N
Next M

End Sub

 

[AnotherHiggins]

mscallisto:

That looks like the very same one I pointed to at Chip Pearson's site (

http://www.cpearson.com/excel/sortws.htm).

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[mscallisto]

anotherhiggins

Exactly, I did get it from an old TT thread...and I really can spell forum !!!

 

[AnotherHiggins]

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886