Sorting Sheets in a Workbook–How?

Topic

New Reply

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

Reply | Quote

Replies

Brett,

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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)

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Brett:

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.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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!

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Loop

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

Loop

End Sub

Dean

Reply | Quote

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

http://www.cpearson.com/excel/sortws.htm%5B/url%5D

Reply | Quote

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

Reply | Quote

Hi,
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.

Reply | Quote

Rory,

Can you make this code available to us all?

Dean

Reply | Quote

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:
frmSheetSelect.Show

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.
list_box.Clear
For i = 1 To num_items
list_box.AddItem values(i)
Next ‘i
Exit Sub
err_hndl:
MsgBox Err & “: ” & Err.Description

End Sub

Hope that helps.

Reply | Quote

gwhitfield,

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:
http://www.j-walk.com/ss/excel/tips/index.htm%5B/url%5D
http://www.erlandsendata.no/english/index.htm%5B/url%5D

Reply | Quote

Thanks cri,

there’s always something you can learn. Thanks. And for the links.

Reply | Quote