Putting Worksheets in Alphabetical Order

[Reba]

Just wondering if any one knows a quick way of
alphabetising worksheets.

I have a spreadsheet with 80+ sheets and they are slowly
becoming out of order, is there a way of doing this at the
touch of a button??

Thanks


Rebecca

 

[Shailesh Shah]

Hi Reba,

Besides David's sugestion, you can also find codes by Chip Pearson that sort
sheet among selected sheets.

http://www.cpearson.com/excel/sortws.htm


Here is also another vba code to sort sheet tabs. Change Worksheets to
Sheets to sort all type of sheets.

Sub QuickSortSheets(Optional SortOrder)
'by Shailesh Shah
Dim i As Long
Dim j As Long
Dim SheetsCount As Long
Dim FirstSheet As String
Dim NextSheet As String
Dim LValue As String
Dim HValue As String
Dim VTemp As String
Application.ScreenUpdating = 0
SheetsCount = Worksheets.Count
For i = 1 To SheetsCount \ 2
Sorted = True
FirstSheet = Worksheets(i).Name
LValue = FirstSheet
HValue = FirstSheet
For j = i To SheetsCount - 1
NextSheet = Worksheets(j + 1).Name
If LValue > NextSheet Then LValue = NextSheet
If HValue < NextSheet Then HValue = NextSheet
Next
If IsMissing(SortOrder) Then
Else
VTemp = LValue
LValue = HValue
HValue = VTemp
End If
If LValue <> FirstSheet Then Worksheets(LValue).Move
before:=Worksheets(i)
If HValue <> Worksheets(SheetsCount).Name Then
Worksheets(HValue).Move after:=Worksheets(SheetsCount)
SheetsCount = SheetsCount - 1
Next
Application.ScreenUpdating = 1
End Sub


Regards,
Shah Shailesh
http://members.lycos.co.uk/shahweb/

 

[Shailesh Shah]

Hi David,

If "SortOrder" is missing then it will sort ascending otherwise if any
arguments found then sort descending.

e.g. QuickSortSheets "D" or any arguments will sort sheets in descending
order.

User can adopt different logic and change code for this.

I have also posted below code in this NG. In fact there are various method
you can find by google search but which one is faster is difficult to say.
But I belive sort sheets with using array is faster.

Sub Test()
Application.ScreenUpdating = 0
Dim l As Integer
Dim h As Integer
l = 1
h = Sheets.Count
Start = Timer

procSortSheet1 "A", l, h
'to sort descending Replace "A" with "D" or anything

Application.ScreenUpdating = 1
MsgBox "Sorting Time : " & Timer - Start
End Sub


Sub procSortSheet(sOrder As String, iLow1 As Integer, iHigh1 As Integer)

'Original Code written by Stephen Bullen for Sorting Array.
'Modified for Sorting Sheets Directly.

On Error Resume Next

'Dimension variables
Dim iLow2 As Integer, iHigh2 As Integer, i As Integer
Dim vItem1 As Variant

'It is good to put below new codes(19 lines) to your main
'macro to check if sheets are not sorted then run
'ProcSortSheet.

Dim Sorted As Boolean
Sorted = True
If sOrder = "A" Then
For i = iLow1 To iHigh1 - 1
If Sheets(i).Name > Sheets(i + 1).Name Then
Sorted = False
Exit For
End If
Next i
Else
For i = iLow1 To iHigh1 - 1
If Sheets(i).Name < Sheets(i + 1).Name Then
Sorted = False
Exit For
End If
Next i
End If
If Sorted Then Exit Sub

'Set new extremes to old extremes
iLow2 = iLow1
iHigh2 = iHigh1

'Get value of array item in middle of new extremes
vItem1 = Sheets((iLow1 + iHigh1) \ 2).Name

'Loop for all the items in the array between the extremes
While iLow2 < iHigh2

If sOrder = "A" Then
'Find the first item that is greater than the mid-point item
While Sheets(iLow2).Name < vItem1 And iLow2 < iHigh1
iLow2 = iLow2 + 1
Wend

'Find the last item that is less than the mid-point item
While Sheets(iHigh2).Name > vItem1 And iHigh2 > iLow1
iHigh2 = iHigh2 - 1
Wend
Else
'Find the first item that is less than the mid-point item
While Sheets(iLow2).Name > vItem1 And iLow2 < iHigh1
iLow2 = iLow2 + 1
Wend

'Find the last item that is greater than the mid-point item
While Sheets(iHigh2).Name < vItem1 And iHigh2 > iLow1
iHigh2 = iHigh2 - 1
Wend
End If

'If the two items are in the wrong order, swap the rows
If iLow2 < iHigh2 Then

Sheets(iHigh2).Move after:=Sheets(iLow2)

Sheets(iLow2).Move after:=Sheets(iHigh2)

End If

'If the pointers are not together, advance to the next item
If iLow2 <= iHigh2 Then
iLow2 = iLow2 + 1
iHigh2 = iHigh2 - 1
End If
Wend

'Recurse to sort the lower half of the extremes
If iHigh2 > iLow1 Then procSortSheet sOrder, iLow1, iHigh2

'Recurse to sort the upper half of the extremes
If iLow2 < iHigh1 Then procSortSheet sOrder, iLow2, iHigh1

End Sub


----------------------------------------------------------------------------
-------------------
Another way is to call Stephen's one dimension Array sorting directly which
may be faster then direct sort.

option base 1

Sub SortSheet(Optional sortorder)

Application.ScreenUpdating = 0
Dim shtarray()
Dim i As Integer
Dim ihigh As Integer
Dim ilow As Integer

ReDim shtarray(Sheets.Count)

For i = 1 To Sheets.Count
shtarray(i) = Sheets(i).Name
Next i

ilow = LBound(shtarray)
ihigh = UBound(shtarray)

If IsMissing(sortorder) Then
Call procSort1D(shtarray, "A", ilow, ihigh)
Else
Call procSort1D(shtarray, "D", ilow, ihigh)
End If

For i = 1 To Sheets.Count
Sheets(CStr(shtarray(i))).Move before:=Sheets(i)
Next i

Erase shtarray

Application.ScreenUpdating = 1

End Sub



Regards,
Shah Shailesh

Hi Shailesh,
Didn't realize that Chip's would sort within a contiguous selection
of sheets (grouped sheets). Thanks for pointing that out.

Also notice he has a sort sheets on color tab as well on that page.

Since you posted your code for sorting worksheets, is there something
you want to point out about it that is different/unique about it.
What is "SortOrder" optional parameter supposed to do..

Another sort worksheets that I like was one to sort by groups
(chart/module/worksheet), but since module sheets were dropped
in Excel 97 it doesn't make much difference.
http://google.com/groups?oi=djq&ic=1&selm=an_489871760
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Besides David's sugestion, you can also find codes by Chip Pearson that sort
sheet among selected sheets.
http://www.cpearson.com/excel/sortws.htm

Here is also another vba code to sort sheet tabs. Change Worksheets to
Sheets to sort all type of sheets. [clipped]

 

[David McRitchie]

Hi Shailesh,
What I was really getting at was that you can only use a parameter
if you invoke the macro from another macro. Sorry, that wasn't a
fair question.

 

[Shailesh Shah]

Hi David,

I have no problem manually assigning macro with one parameter to my buttons
in my custom toolbar. I am also using onaction property to assign macro
(with parameter) to controlbutton at run-time. Is this is the Issue?

Regards,
Shah Shailesh