+ Reply to Thread
Results 1 to 4 of 4

Sort EXCEL sheets into alphabetical order

  1. #1
    KymY
    Guest

    Sort EXCEL sheets into alphabetical order

    I have a workbook with 50 sheets (1 for each employee).

    How can I get Excel to sort these into alphabetical order ?

    (From kyeoward@hotmail.com)


  2. #2
    Rowan
    Guest

    RE: Sort EXCEL sheets into alphabetical order

    This macro should do it.

    Sub SortSheets()

    Dim sht As Worksheet
    Dim mySht As Worksheet
    Dim i As Integer
    Dim endRow As Long
    Dim shtNames As Range
    Dim Cell As Range

    Set mySht = Sheets.Add
    mySht.Move before:=Sheets(1)

    For i = 2 To Sheets.Count
    mySht.Cells(i - 1, 1).Value = Sheets(i).Name
    Next i

    endRow = mySht.Cells(Rows.Count, 1).End(xlUp).row
    Set shtNames = mySht.Range(Cells(1, 1), Cells(endRow, 1))

    shtNames.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _
    xlNo, OrderCustom:=1

    i = 2
    For Each Cell In shtNames
    Sheets(Cell.Value).Move before:=Sheets(i)
    i = i + 1
    Next Cell

    Application.DisplayAlerts = False
    mySht.Delete
    Application.DisplayAlerts = True

    End Sub


    Regards
    Rowan

    "KymY" wrote:

    > I have a workbook with 50 sheets (1 for each employee).
    >
    > How can I get Excel to sort these into alphabetical order ?
    >
    > (From kyeoward@hotmail.com)
    >


  3. #3
    KymY
    Guest

    RE: Sort EXCEL sheets into alphabetical order



    "KymY" wrote:

    > I have a workbook with 50 sheets (1 for each employee).
    >
    > How can I get Excel to sort these into alphabetical order ?
    >
    > (From kyeoward@hotmail.com)
    > Thanks, mate - have pasted the script into my Lotus Organizer Tips notepad


  4. #4
    David McRitchie
    Guest

    Re: Sort EXCEL sheets into alphabetical order

    Sorting worksheet tabs into alphabetical order:

    A list of worksheets in a worksheet may be useful, but it is
    not necessary in order to sort the worksheets, and since that
    worksheet is deleted anyway wouldn't seem practical.

    see SortAllSheets macro in
    http://www.mvps.org/dmcritchie/excel/buildtoc2.htm .
    --
    HTH,
    David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
    My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
    Search Page: http://www.mvps.org/dmcritchie/excel/search.htm



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1