Excel-Sort sheets alphabetically.

[Copierbw]

I have one workbook with a lot of sheets that I would like to sort alphabetically and automatic. I can drag them and drop them at the bottom of the workbook but sorting them manually when you have 24 sheets just too much. Is there a way to do this task?
Thanks...

You don't need eyes to see just the vision because there are always more ways and different answers to what we are used to...

 

[SkipVought]

Hi,

You'll have to use VBA...

Sub sortsheets()
    Dim wsTemp As Worksheet
'insert a temporary sheet to collect & sort sheet names
    Set wsTemp = Worksheets.Add
    r = 1
    With wsTemp
        For Each ws In Worksheets
            If ws.Name <> .Name Then
                .Cells(r, 1).Value = ws.Name
                r = r + 1
            End If
        Next
        .Cells(1, 1).CurrentRegion.Sort _
            Key1:=wsTemp.Cells(1, 1)
'order the sheets
        For Each c In .Cells(1, 1).CurrentRegion
            Worksheets(c.Value).Move _
                Before:=Sheets(c.Row)
        Next
    End With
'delete temp sheet
    Application.DisplayAlerts = False
    wsTemp.Delete
End Sub

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[RamziSaab]

here copy and paste this code and run it, it should do the trick

Sub Arrange_Sheets()
Dim ws As Worksheet
Dim strsheetname As String
Dim i As Integer
Dim r As Long

Workbooks("Book1.xls&quot.Sheets.Add(Sheet1).Name = "temp"
With Workbooks("Book1.xls&quot.Worksheets("Temp&quot
For Each ws In Workbooks("book1.xls&quot.Worksheets


If ws.Name = "temp" Then
Else
i = i + 1
.Cells(i, 1) = ws.Name
End If
Next

Columns("A:A&quot.Select
Selection.Sort Key1:=Range("A1&quot, Order1:=xlDescending
r = .UsedRange.Rows.Count

For i = 1 To r 'number of rows

strsheetname = .Cells(i, 1).Value
Sheets(strsheetname).Move Before:=Sheets(1)
Next

Application.DisplayAlerts = False

Sheets("temp&quot.Delete
Application.DisplayAlerts = True
End With
End Sub

 

[RamziSaab]

Copierbw :
By the way forgot to mention u have to change BOOK1.xls to the name of your workbook!

 

[KenWright]

Just another bit of code to do the same thing. Site is well worth bookmarking for any that don't already have Chip's site in their list of favourites.

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

Regards
Ken...................

 

[avayaman]

I'd like to do the same, but sort sheets based on a number placed somwewhere on the sheet itself, such as in cell Z1 for example, rather than on the sheet name.

Paul Beddows
Avaya Implementation
Telus
Vancouver, Canada
E-mail via

http://www.vancouver.hm/email.html

 

[Zathras]

Here is one way:
[blue]

Option Explicit

Sub SortWorksheets()
Const SORT_BY_CELL = "Z1"

[green]

 ' <--- Modify to indicate sort key

[/color]

Const SORT_BY = 1
Const SHEET_NAME = 2
Dim wks As Worksheet
Dim WorkArray() As String
Dim nWorksheets As Integer
Dim i As Integer
Dim j As Integer
Dim sSortBy As String
Dim sSheetName As String

  nWorksheets = Worksheets.Count
  If nWorksheets > 1 Then

[green]

    ' Load array with sort keys and sheet names

[/color]

    ReDim WorkArray(nWorksheets, 2)
    For Each wks In Worksheets
      i = i + 1
      WorkArray(i, SORT_BY) = wks.Range(SORT_BY_CELL)
      WorkArray(i, SHEET_NAME) = wks.Name
    Next wks

[green]

    ' Sort array (descending)

[/color]

    For i = 1 To nWorksheets - 1
      For j = i + 1 To nWorksheets
        If WorkArray(j, SORT_BY) > WorkArray(i, SORT_BY) Then
          sSortBy = WorkArray(i, SORT_BY)
          sSheetName = WorkArray(i, SHEET_NAME)
          WorkArray(i, SORT_BY) = WorkArray(j, SORT_BY)
          WorkArray(i, SHEET_NAME) = WorkArray(j, SHEET_NAME)
          WorkArray(j, SORT_BY) = sSortBy
          WorkArray(j, SHEET_NAME) = sSheetName
        End If
      Next j
    Next i

[green]

    ' Re-arrange sheets

[/color]

    For i = 2 To nWorksheets
      Sheets(WorkArray(i, SHEET_NAME)).Move Before:=Sheets(1)
    Next i
    
  End If
End Sub

[/color]