Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Lounger
    Join Date
    Mar 2002
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting Sheets in a Workbook--How?

    I have a workbook with a lot of sheets in it--about 100. (Each sheet is for a closed client file.) I would like to sort the sheets by the tabs containing their name, but can't figure out a way to do it. Does anyone have any suggestions?

    Brett

  2. #2
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Sheets in a Workbook--How?

    Brett,

    you can reorder the sheets by using the Edit, Move or Copy command from the menu.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Sorting Sheets in a Workbook--How?

    ... or just drag the tabs into order, but Excel has no built in way to sort tabs.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Lounger
    Join Date
    Mar 2002
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Sheets in a Workbook--How?

    That's the problem--I've got about 100 sheets in no order at all, and would like to put them in alphabetical order by tab (without having to drag each one to the appropriate place).

    Brett

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Brantford, Ontario, Canada
    Posts
    2,391
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Sheets in a Workbook--How?

    I know it's a long, drawn out process [img]/w3timages/icons/frown.gif[/img], but what if you export each worksheet to a file of it's own, then create a new Excel spreadsheet and import them, in alphabetical order?

    May also be able to do this with a VBA script, but I'm not your contact for that...

    <font class="bigger"><font class="bigger"><font color=008080>Chris (cbaldrey)</font color=008080></font size=big></font size=big>
    Christopher Baldrey

  6. #6
    Lounger
    Join Date
    Mar 2002
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Sheets in a Workbook--How?

    Yeah, I guess I could <groan>, but that seems like a little too much for this problem. I was hoping there would be an easy solution.

  7. #7
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Sheets in a Workbook--How?

    Is this a one-off exercise or an ongoing one?

    It can be coded in VBA, but it's probably not worth it if you're only doing it once. It will take longer to write the code than to do it manually- tedious as it may be.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  8. #8
    Lounger
    Join Date
    Mar 2002
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Sheets in a Workbook--How?

    Pretty much a one shot deal, although we will continue to move sheets as their cases close to this file.

  9. #9
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sorting Sheets in a Workbook--How?

    Brett:

    John Walkenbach's <font color=red><font class="bigger">EXCELLENT</font size=big></font color=red> book Microsoft Excel 2000 - Power Progamming with VBA has a discussion of this very topic in chapter 9 (pp 226-237) and walks the reader through the development process for a utility to sort worksheets in alphabetic order.

    The eventual program is included in the book, as well as on a companion CD-ROM (as is a slightly improved version). I am reluctant to violate John's copyright (at least publicly <g>) but if you pick up a copy in the library there is only about a page of code to re-type, even if you don't get the CD-ROM. The book is a terrific reference if you are going to be doing any VBA development.

  10. #10
    Lounger
    Join Date
    Dec 2000
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Sheets in a Workbook--How?

    Following is some VBA code that loops through all sheets in a workbook (except for several hard coded ones), enters the sheet names into a separate sheet (called TOC) and then sorts the TOC entries in order by sheet name. The code also adds hyperlinks to the sheet names.
    ************************************************** ****
    Dim counter, nrow, toccount
    Dim thissheet As String
    Dim myblank As String
    Dim myadd As String

    counter = 0
    toccount = 4
    Application.EnableEvents = False

    ' Worksheets("Reports").Activate
    ' ActiveSheet.Protect

    ' loop through sheets bypassing "Raw Data" , "TOC", "Totals Sheet" and "Reports"

    Worksheets("TOC").Activate
    Worksheets("TOC").Unprotect
    Range("a4").Select

    Set tbl = ActiveCell.CurrentRegion

    ' select table without header row

    tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _
    tbl.Columns.Count).Select

    ' clear target area

    Selection.ClearContents


    Do While counter < Sheets.Count
    counter = counter + 1
    thissheet = Sheets(counter).Name
    ' MsgBox "sheet name=" & " " & thissheet
    If thissheet <> "Raw Data" Then
    If thissheet <> "Reports" Then
    If thissheet <> "TOC" Then
    If thissheet <> "Totals Sheet" Then
    ' MsgBox "reached a sheet other than raw data"
    toccount = toccount + 1
    ' Cells(toccount, 1) = thissheet
    Cells(toccount, 1).Select
    Cells(toccount, 1) = thissheet
    myadd = thissheet & "!A1"
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
    myadd
    End If
    End If
    End If
    End If
    Loop

    Worksheets("TOC").Activate

    Worksheets("TOC").Range("A5").Sort _
    Key1:=Worksheets("TOC").Columns("A")
    ************************************************** *******
    HTH. [img]/w3timages/icons/smile.gif[/img]

  11. #11
    Lounger
    Join Date
    Mar 2002
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Sheets in a Workbook--How?

    Carol:

    While I am exceedingly appreciative that you took the time and effort to write this macro, it, er, doesn't work. It stops on the "Worksheets("TOC").Activate" line with a "Run-time error '9': Subscript out of range" error. Any thoughts as to what is causing the problem?

  12. #12
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Sheets in a Workbook--How?

    Interlaw,

    You usually get that when the worksheet name has not been found. Try creating a worksheet with the name "TOC" and try running it again.

    By the way, as Carol has mentioned, it still doesn't sort your sheets- but it does give you a nice way of navigating through them. Nice lateral thinking Carol!
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  13. #13
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Sorting Sheets in a Workbook--How?

    Again, this doesn't sort, but here's two simple bits of code, kind of subsets of Carol's, I use a lot to work with multiple sheets. The first one, authored by Chip Pearson (I hope he won't mind), lists the sheet names, which can then be addressed by =indirect(). The second one unhides all sheets (since I work with some people who meddle with what they don't understand, I hide sheets quite often).

    Sub ListSheetNames()
    'Within the FOR loop there are two statements. The first procedure
    'will list all worksheet names in a ROW (starting in the active column
    'and moving to the right); it is commented out (it won't run).
    'The second will list the sheet names in COLUMN (starting in the active
    'row, and moving down).

    Dim Ndx As Integer
    Dim ColNdx As Integer
    Dim RowNdx As Long

    ColNdx = ActiveCell.Column
    RowNdx = ActiveCell.Row

    For Ndx = 1 To Worksheets.Count
    ' Cells(RowNdx, ColNdx + Ndx - 1).Value = Worksheets(Ndx).Name
    Cells(RowNdx + Ndx - 1, ColNdx).Value = Worksheets(Ndx).Name
    Next Ndx
    End Sub
    ---
    Sub UnhideAllSheets()
    For Each Sheet In ActiveWorkbook.Sheets
    If Sheet.Visible = False Then Sheet.Visible = True
    Next
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  14. #14
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Sheets in a Workbook--How?

    This might work. A new worksheet labelled "00000" is added - hopefully that will fall alphabetically before all other sheets. A listing of all Tab names is made, sorted, then the worksheets are moved in reverse order to the front. Last thing that happens is that Sheet 00000 is deleted - you have to manually accept this.
    A bit mickey-mouse, but does seem to work.

    Sub SortTabs()
    Dim a(200)
    Worksheets(1).Activate
    Sheets.Add
    ActiveSheet.Name = "00000"
    n = 0
    For Each w In Worksheets
    n = n + 1: a(n) = w.Name
    Cells(n, 1) = a(n)
    Next w
    Columns("A:A").Select
    Selection.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    For x = 1 To n
    a(x) = Cells(x, 1)
    Next
    For x = 2 To n
    Sheets(a(x)).Select
    Sheets(a(x)).Move Before:=Sheets(1)
    Next
    Worksheets(n).Activate
    ActiveWindow.SelectedSheets.Delete
    Worksheets(1).Activate
    End Sub

  15. #15
    New Lounger
    Join Date
    Jan 2001
    Location
    Arlington, Virginia, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Sheets in a Workbook--How?

    I modified this code a little to sort just the worksheets. I dropped the index page of TOC requirements and just move the worksheets into alphabetic order. Try this one:

    Sub WorksheetInOrderSort()

    Dim intCounter
    Dim intCounter2
    Dim intSwitch

    intCounter = 0
    intCounter2 = 0

    Do While intCounter < Sheets.Count

    intCounter = intCounter + 1
    intCounter2 = intCounter + 1
    intSwitch = intCounter
    Do While intCounter2 <= Sheets.Count

    If Sheets(intSwitch).Name > Sheets(intCounter2).Name Then
    intSwitch = intCounter2
    End If

    intCounter2 = intCounter2 + 1

    Loop

    Sheets(intSwitch).Select
    Sheets(intSwitch).Move Before:=Sheets(intCounter)

    Loop


    End Sub



    Dean

Page 1 of 2 12 LastLast

Posting Permissions

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