• 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?


    Viewing 2 reply threads
    • #509826


      you can reorder the sheets by using the Edit, Move or Copy command from the menu.

      • #509827

        … or just drag the tabs into order, but Excel has no built in way to sort tabs.

        • #509829

          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).


          • #509830

            I know it’s a long, drawn out process , 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…

            Chris (cbaldrey)

            • #509833

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

          • #509834

            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.

    • #509845


      John Walkenbach’s EXCELLENT 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 ) 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.

    • #509847

      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”


      Set tbl = ActiveCell.CurrentRegion

      ‘ select table without header row

      tbl.Offset(1, 0).Resize(tbl.Rows.Count – 1, _

      ‘ clear target area


      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:= _
      End If
      End If
      End If
      End If


      Worksheets(“TOC”).Range(“A5”).Sort _

      • #509853


        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?

        • #509854


          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!

          • #509885

            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
            End Sub

        • #509891

          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)
          ActiveSheet.Name = “00000”
          n = 0
          For Each w In Worksheets
          n = n + 1: a(n) = w.Name
          Cells(n, 1) = a(n)
          Next w
          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)
          For x = 2 To n
          Sheets(a(x)).Move Before:=Sheets(1)
          End Sub

        • #509979

          I should have clarified that the routine was written a while ago for my particular workbook. In my case, the worksheet “TOC” needs to exist in order for the code to work. No doubt there are a few other tweaks that need to be made in order to customize the code to your application.

      • #509893

        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(intCounter2).Name Then
        intSwitch = intCounter2
        End If

        intCounter2 = intCounter2 + 1


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


        End Sub


      • #510012

        A late post, but try this code from this stellar site:


        • #510019

          Hi cri,

          A nice link. There’s some useful things in there. Thanks for that. I’ll be looking at the “compare workbooks” stuff, to see how it stacks up.

          As regarding the “sort worksheets” solutions by carol http://www.wopr.com/cgi-bin/w3t/showthread…5&vc=1#Post2955%5B/url%5D

          I liked that solution because, although it didn’t address the problem directly (How do I sort worksheets?) it gave a solution which probably made it a lot easier to navigate. So instead of having to scroll horizontally scroll through 100 worksheets (however, now in alphabetical order) I can now select a single “contents” sheet, and then select the sheet I want from there.

          You could even have a button to go to sheet “toc”- or even prefix sheet “toc” with a prefix so that is showed at the beginning of a workbook- it would make much nicer navigation

          • #510024

            Just as an addendum to that, I have a userform with a listbox on it that I can call via a shortcut menu from any sheet in any workbook, which lists all the sheets in the current workbook in alphabetical order and you simply double-click on a sheet name to go to it. I have a lot of workbooks with numerous sheets in so I figured I needed a generic solution.
            Just a thought.

            • #510028


              Can you make this code available to us all?


            • #510044

              Hi Dean,
              I’ve attached the userform, which needs to be inserted into a personal macro workbook or similar. It can be called by a simple macro:

              the rest of the code can be copied into a macro workbook module (the SelectionSort code is pretty generic and can be used to sort lots of things with a little modification):
              Sub Selectionsort(values() As String, _
              ByVal min As Long, _
              ByVal max As Long)
              Dim i As Long
              Dim j As Long
              Dim smallest_value As String
              Dim smallest_j As Long

              For i = min To max – 1
              ‘ Find the smallest remaining value in entries
              ‘ i through num.
              smallest_value = values(i)
              smallest_j = i

              For j = i + 1 To max
              ‘ See if values(j) is smaller.
              If values(j) < smallest_value Then
              ' Save the new smallest value.
              smallest_value = values(j)
              smallest_j = j
              End If
              Next 'j

              If smallest_j i Then
              ‘ Swap items i and smallest_j.
              values(smallest_j) = values(i)
              values(i) = smallest_value
              End If

              Next ‘i

              End Sub
              ‘ Sort the items in the ListBox.
              Sub SortListBox(list_box As MSForms.ListBox)
              On Error GoTo err_hndl:

              Dim values() As String
              Dim num_items As Integer
              Dim i As Integer
              ‘ Put the list choices in a string array.
              num_items = list_box.ListCount

              ReDim values(1 To num_items)

              For i = 1 To num_items
              values(i) = list_box.List(i – 1)
              Next ‘i

              ‘ Sort the list.
              Selectionsort values, 1, num_items

              ‘ Put the items back in the ListBox.
              For i = 1 To num_items
              list_box.AddItem values(i)
              Next ‘i
              Exit Sub
              MsgBox Err & “: ” & Err.Description

              End Sub

              Hope that helps.

          • #510060


            You do not have to navigate _horizontaly_ : A right click on the navigation arrows and a click on “More sheets” will bring up an (unsorted) listbox with the worksheets …

            As for the links: I have some more, which you might know already:

    Viewing 2 reply threads
    Reply To: Sorting Sheets in a Workbook–How?

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: