sorting worksheets

69 views
Skip to first unread message

The Sgiliau Team

unread,
Jun 15, 1999, 3:00:00 AM6/15/99
to
I know how to manually sort worksheets,
but can the process be automated?
I need them in alphabetical order.
Thanx

Graham

--
An approach to embedding ICT skills in a rural economy
mailto:sgi...@harlech.ac.uk


Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.

Patrick Molloy

unread,
Jun 15, 1999, 3:00:00 AM6/15/99
to
the following sub adds a sheet and copies the worksheet names into it.
it then uses Excels sort routine ( why re-invent the wheel?) and
finally moves the sheets.

Sub sortsheets()
'add a new sheet and call it temp
Worksheets.Add
ActiveSheet.Name = "temp"
' count how many sheets
n = Worksheets.Count
'now copy the names to temp
For i = 1 To n
Cells(i, 1) = Worksheets(i).Name
Next
' sort alphabetically
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, _
header:=guess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

' now read the names
Dim sn()
ReDim sn(1 To n)
For i = 1 To n
sn(i) = Cells(i, 1)
Next
'...and move the sheets
For i = n To 1 Step -1
Worksheets(sn(i)).Move before:=Sheets(1)
Next
' clear off the temp sheet
Application.DisplayAlerts = False
Worksheets("temp").Delete
End Sub

Patrick
_____________

In article <7k5f35$k42$1...@nnrp1.deja.com>,

David McRitchie

unread,
Jun 15, 1999, 3:00:00 AM6/15/99
to
Hi Graham, (posted with email copy)
Here are some macros of interest. I prefer to use Jeff Webb's example for
sorting which sorts sheets within their sheet type. Particularly useful in
XL95 where modules are seen on the sheet tabs.

I had been perplexed into thinking John Manville's SortSheets did the same but
what I had not seen was that it sorted worksheets only leaving the others in
place as they were. The modified SortALLSheetes will sort all sheets
alphabetically regardless of type.

EnumerateSheets will create a list of sheets and their type in the order you
have them in your workbook.

For those who have never installed a macro see orange shaded area in
http://members.aol.com/dmcritchie/excel/formula.htm  
If line turns to RED it denotes syntax error and probably belongs to line
above.      

'EnumerateSheets - list of sheets from active cell down, uses two columns
'For reordering sheets, I prefer Jeff Web's "ReOrderSheets"

'ReOrderSheets - reorders sheets within Type
' Types: Worksheet, Chart, Module, Dialogsheet
'SortSheets - reorders only Worksheets
'SortALLSheets - reorders ALL sheets, regardless of type

Sub EnumerateSheets()
'listed from active cell down 2-cols -- DMcRitchie 1999-03-04
Application.Calculation = xlManual 'xl97 up use xlCalculationManual  
Application.ScreenUpdating = False
cRow = ActiveCell.Row
cCol = ActiveCell.Column
For csht = 1 To ActiveWorkbook.Sheets.Count 'worksheet or sheets
cells(cRow - 1 + csht, cCol) = Sheets(csht).Name
cells(cRow - 1 + csht, cCol + 1) = TypeName(Sheets(csht))
Next csht
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic 'xl97 up use xlCalculationAutomatic
End Sub

Sub ReOrderSheets()
' page 48 in "Using Excel VBA" Jeff Webb
'for each sheet in a workbook ...
For Each shtcount In Sheets
'group the different types of sheets together
Select Case TypeName(shtcount)
Case "Worksheet"
iWrk = iWrk + 1
shtcount.Move After:=Sheets(iWrk)
Case "Chart"
iChrt = iChrt + 1
shtcount.Move After:=Sheets(Worksheets.Count + iChrt)
Case "Module"
imod = imod + 1
shtcount.Move After:=Sheets(Worksheets.Count + Charts.Count + imod)
Case "DialogSheet"
iDlg = iDlg + 1
shtcount.Move After:=Sheets(Worksheets.Count + Charts.Count +
Modules.Count + iDlg)
End Select
Next shtcount
End Sub

Sub SortSheets()
'sort worksheets within a workbook in Excel 7 -- Bill Manville
Dim iSheet As Integer, iBefore As Integer
MsgBox ActiveWorkbook.Worksheets.Count
For iSheet = 1 To ActiveWorkbook.Worksheets.Count
Worksheets(iSheet).Visible = True
For iBefore = 1 To iSheet - 1
If UCase(Worksheets(iBefore).Name) > UCase(Worksheets(iSheet).Name) Then
ActiveWorkbook.Worksheets(iSheet).Move
Before:=ActiveWorkbook.Worksheets(iBefore)
Exit For
End If
Next iBefore
Next iSheet
End Sub

Sub SortALLSheets()
'sort sheets within a workbook in Excel 7 -- Bill Manville
'modified to sort all sheets instead of just worksheets
Dim iSheet As Integer, iBefore As Integer
MsgBox ActiveWorkbook.Sheets.Count
For iSheet = 1 To ActiveWorkbook.Sheets.Count
Sheets(iSheet).Visible = True
For iBefore = 1 To iSheet - 1
If UCase(Sheets(iBefore).Name) > UCase(Sheets(iSheet).Name) Then
ActiveWorkbook.Sheets(iSheet).Move
Before:=ActiveWorkbook.Sheets(iBefore)
Exit For
End If
Next iBefore
Next iSheet
End Sub

HTH,
David McRitchie
My Excel Pages: http://members.aol.com/dmcritchie/excel/excel.htm

Ogilvy, Thomas, W., Mr., ODCSLOG

unread,
Jun 15, 1999, 3:00:00 AM6/15/99
to

Here is a reference with sample code:

http://support.microsoft.com/support/kb/articles/q105/0/69.asp
XL: Macros to Sort Tabs in the Active Workbook

Chip Pearson has a similar routine at his web site
http://home.gvi.net/~cpearson/excel.htm

HTH,
Tom Ogilvy


-----Original Message-----
From: The Sgiliau Team [mailto:sgi...@harlech.ac.uk]
Posted At: Tuesday, June 15, 1999 7:57 AM
Posted To: misc
Conversation: sorting worksheets
Subject: sorting worksheets


I know how to manually sort worksheets,
but can the process be automated?
I need them in alphabetical order.

Reply all
Reply to author
Forward
0 new messages