Hi 'meLord
This is a direct copy and paste from the Pearson people (an excellent Excel Site -
www.cpearson.com/excel.htm)
Sorting Worksheets In A Workbook
Sorting Worksheets In Alphabetical Order
In some applications, it may be useful to have the worksheets in alphabetical order. For example, if you have a worksheet for each employee on a team and each employee has their own worksheet, you may want these sheets in alphabetical order. You could do this manually, but if you have more than a few sheets, it would be easier to automate the task. Excel does not have a built in tool to do this, but you can use some fairly simple VBA code accomplish this.
The following code will sort the sheets in the workbook, in alphabetical order.
Code:
Sub SortWorksheets()
Dim N As Integer
Dim M As Integer
Dim FirstWSToSort As Integer
Dim LastWSToSort As Integer
Dim SortDescending As Boolean
SortDescending = False
If ActiveWindow.SelectedSheets.Count = 1 Then
FirstWSToSort = 1
LastWSToSort = Worksheets.Count
Else
With ActiveWindow.SelectedSheets
For N = 2 To .Count
If .Item(N - 1).Index <> .Item(N).Index - 1 Then
MsgBox "You cannot sort non-adjacent sheets"
Exit Sub
End If
Next N
FirstWSToSort = .Item(1).Index
LastWSToSort = .Item(.Count).Index
End With
End If
For M = FirstWSToSort To LastWSToSort
For N = M To LastWSToSort
If SortDescending = True Then
If UCase(Worksheets(N).Name) > UCase(Worksheets(M).Name) Then
Worksheets(N).Move Before:=Worksheets(M)
End If
Else
If UCase(Worksheets(N).Name) < UCase(Worksheets(M).Name) Then
Worksheets(N).Move Before:=Worksheets(M)
End If
End If
Next N
Next M
End Sub
As written above, the code will sort all of the worksheets in ascending order. To sort in descending order, you can change SortDescending to True. You may not want to sort all of the sheets. For example, if you have a summary sheet at either the beginning or end of the workbook, you may not want to include this in the sort. To start the sort after the one or more sheets, change the value of FirstWSToSort to the index number of the first worksheet to sort. For example, to leave the first two worksheets in place, change the value to 3. Similarly, to leave the last two sheets in place, change the value of LastWSToSort to Worksheets.Count - 2.
If you don't know what the worksheet index number is, or you want to use the worksheet name instead of the index number, you can retrieve the sheet's index number with the Index property. For example,
Code:
FirstWSToSort = Worksheets("SomeSheet").Index
You can also select the sheets to sort by clicking on the tab of the first sheet to sort, holding down the SHIFT key, and clicking the tab of the last sheet to sort. The code will then sort only those sheets. The sheets to sort must be adjacent. You cannot sort non-adjacent sheets.
Sorting In Custom Order
You can also sort worksheets in a custom order. For example, if you have the name of your worksheets in cells A1:A3 on a sheet named CSheet, in the order you want them sorted, use code like the following to move the sheets in to the proper order:
Code:
Sub SortWS2()
Dim SortOrder As Variant
Dim Ndx As Long
With Worksheets("CSheet").Range("A1:A3")
For Ndx = .Cells.Count To 1 Step -1
Worksheets(.Cells(Ndx).Value).Move before:=Worksheets(1)
Next Ndx
End With
End Sub
You can use similar code if you have worksheet names in an array, in the order you want the arranged. The code below will arrange the sheets in the order in which their names appear in the array variable SortOrder.
Code:
Sub SortWS3()
Dim SortOrder As Variant
Dim Ndx As Long
SortOrder = Array("CSheet", "ASheet", "BSheet")
For Ndx = UBound(SortOrder) To LBound(SortOrder) Step -1
Worksheets(SortOrder(Ndx)).Move before:=Worksheets(1)
Next Ndx
End Sub
Grouping Sheets By Color
Excel 2002 and later versions allow you to color the worksheet tabs. The code below will group the sheets according to color. Within each color group, the original order is preserved (e.g., blue sheets will be grouped together, but with the group of blue sheets, the order will be the same as before the sheets were grouped). This will work only in Excel 2002 and later. Earlier version do not allow colored tabs, so the code won't work.
Code:
Sub GroupSheetsByColor()
Dim Ndx As Long
Dim Ndx2 As Long
For Ndx = 1 To Worksheets.Count - 1
For Ndx2 = Ndx To Worksheets.Count
If Worksheets(Ndx2).Tab.ColorIndex = _
Worksheets(Ndx).Tab.ColorIndex Then
Worksheets(Ndx2).Move after:=Worksheets(Ndx)
End If
Next Ndx2
Next Ndx
End Sub
Try that...
anvil19
.Name) > UCase(Sheets(y + 1).Name) Then
