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