xQQME,
http://www.google.com/groups?q=sort...TF-8&selm=upJpM0hlCHA.1912@tkmsftngp04&rnum=1
Here is the first solution I found in Google search. There are very likely
others as well.
Hopet this helps.
Regards,
Kevin
From: Patrick Molloy (
[email protected])
Subject: Re: Sort Sheets very Quickly
View: Complete Thread (8 articles)
Original Format
Newsgroups: microsoft.public.excel.programming
Date: 2002-11-27 06:02:04 PST
This is a bit like re-inventing the wheel. Excel has a very fast sort
capability already, so use it.
All you need to do is drop the sheets' names into a column, sort the column,
then simply move the sheets.
I agree with Rob Bruce that really, its how efficient is Excel at moving the
sheets that may be the clincher here though.
The folowing code also sorts the sheets, but there's really only 4 lines
that count = the SORT line and the for...next loop that
Sub SortSheets()
Dim ws As Worksheet
Dim i As Long
Application.ScreenUpdating = False
' get a list of the sheet names in a temp sheet
Set ws = Worksheets.Add
For i = 1 To Worksheets.Count
ws.Cells(i, 1) = Worksheets(i).Name
Next
'sirt the list
ws.Range("A1").CurrentRegion.Sort ws.Range("A1"), xlAscending, , , , , ,
xlNo, , , xlSortColumns
'move sheets
For i = Worksheets.Count To 1 Step -1
Worksheets(ws.Cells(i, 1).Value).Move before:=Worksheets(1)
Next
'remove temp sheet
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Here's my reverse example - it randomly "orders" by first adding a random
number then sorting the list of names.
Sub RandomSort()
Dim ws As Worksheet
Dim i As Long
Set ws = Sheets("Sheet1")
For i = 1 To Worksheets.Count
ws.Cells(i, 2) = Rnd()
ws.Cells(i, 1) = i
Next
ws.Range("A1").CurrentRegion.Sort ws.Range("b1"), xlAscending, , , , , ,
xlNo, , , xlSortColumns
'move sheets
For i = Worksheets.Count To 1 Step -1
Worksheets("sheet" & ws.Cells(i, 1)).Move before:=Worksheets(1)
Next
ws.Activate
End Sub
