Macro for sorting sheets by a cell's value

Chris,

This isn't the prettiest version, but it works (replace all references to Range("A10") if A10 is not really the cell you want to sort by). Hope it helps,

Russell

(I'll also email the code since I don't know HTML - so it will look better than what we get here - everything is left-justified)

Option Explicit

Sub SortSheets()

Dim intI As Integer
Dim xls As Worksheet
Dim avar() As Variant

ReDim avar(1 To 2, 1 To 1)

intI = 1

For Each xls In ActiveWorkbook.Worksheets
Call InsertNames(avar, xls)
intI = intI + 1
Next xls

ReOrderSheets avar

End Sub

Function InsertNames(ByRef avar() As Variant, ByVal xls As Worksheet)

Dim intJ As Integer

If UBound(avar, 2) = 1 And avar(1, 1) = "" Then
avar(1, 1) = xls.Name
avar(2, 1) = xls.Range("A10").Value
Exit Function
Else
ReDim Preserve avar(1 To 2, 1 To UBound(avar, 2) + 1)

For intJ = 1 To UBound(avar, 2)
If intJ = UBound(avar, 2) Then
avar(1, intJ) = xls.Name
avar(2, intJ) = xls.Range("A10").Value
Exit Function
ElseIf xls.Range("A10").Value > avar(2, intJ) Then
ReOrder avar, xls, intJ
Exit Function
End If
Next intJ
End If

End Function

Function ReOrder(ByRef avar() As Variant, ByVal xls As Worksheet, ByVal intI As Integer)

Dim intJ As Integer

For intJ = UBound(avar, 2) To intI + 1 Step -1
avar(1, intJ) = avar(1, intJ - 1)
avar(2, intJ) = avar(2, intJ - 1)
Next intJ

avar(1, intI) = xls.Name
avar(2, intI) = xls.Range("A10").Value

End Function

Function ReOrderSheets(avar() As Variant)

Dim intI As Integer

For intI = 1 To UBound(avar, 2)
Sheets(avar(1, intI)).Move after:=Sheets(ActiveWorkbook.Sheets.Count)
Next intI

End Function