Sort Worksheets Numerically

[PJFry]

I have workbook with 250 sheets and I need to sort them by the total sales on
each sheet.

My first thought was to rename the sheet to the sales value (e.g. Customer
ABC111 had sales of $4,000, so the worksheet name is now 4000). That part
worked fine, but when I use any sorting code, it sorts the sheets in the
alpha code (1,11,111,2,22,222, ect).

Is there a way to sort these sheets numerically?

Thanks!

PJ

 

[Helmut Meukel]

How big can those values get?
Up to $ 9,000,000 ? or more?
I would use something like
shName = Space(7)
ValueTxt = CStr(Value)
pos = 7 - Len(ValueTxt) + 1
Mid(shName, pos) =ValueTxt

So shorter values have more spaces in front of the value
and the alphabetical sorting will now work as you want.
Just use enough spaces to fit the highest possible value.

HTH.

Helmut.

 

[Chip Pearson]

Below is a modification of the SortWorksheetsByName function described
at http://www.cpearson.com/Excel/sortws.aspx. It assumes that ALL the
worksheets, or at least those between FirstToSort and LastToSort, have
strictly numeric names. The code will sort sheets named "11", "1", and
"2" into the numeric order "1", "2", "11". The code will blow up if a
sheet name is not numeric. Adding that logic is left as an excersize
to the reader.


Public Function SortWorksheetsByName(ByVal FirstToSort As Long, ByVal
LastToSort As Long, _
ByRef ErrorText As String, Optional ByVal SortDescending As
Boolean = False) As Boolean
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' SortWorksheetsByName
' This sorts the worskheets from FirstToSort to LastToSort by name
' in either ascending (default) or descending order. If successful,
' ErrorText is vbNullString and the function returns True. If
' unsuccessful, ErrorText gets the reason why the function failed
' and the function returns False.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim M As Long
Dim N As Long
Dim WB As Workbook
Dim B As Boolean

Set WB = Worksheets.Parent
ErrorText = vbNullString

If WB.ProtectStructure = True Then
ErrorText = "Workbook is protected."
SortWorksheetsByName = False
End If

'''''''''''''''''''''''''''''''''''''''''''''''
' If First and Last are both 0, sort all sheets.
'''''''''''''''''''''''''''''''''''''''''''''''
If (FirstToSort = 0) And (LastToSort = 0) Then
FirstToSort = 1
LastToSort = WB.Worksheets.Count
Else
'''''''''''''''''''''''''''''''''''''''
' More than one sheet selected. We
' can sort only if the selected
' sheet are adjacent.
'''''''''''''''''''''''''''''''''''''''
B = TestFirstLastSort(FirstToSort, LastToSort, ErrorText)
If B = False Then
SortWorksheetsByName = False
Exit Function
End If
End If

'''''''''''''''''''''''''''''''''''''''''''''
' Do the sort, essentially a Bubble Sort.
'''''''''''''''''''''''''''''''''''''''''''''
For M = FirstToSort To LastToSort
For N = M To LastToSort
If SortDescending = True Then
If Int(WB.Worksheets(N).Name) >
Int(WB.Worksheets("M").Name) Then
WB.Worksheets(N).Move before:=WB.Worksheets(M)
End If
Else
If Int(WB.Worksheets(N).Name) < Int(WB.Worksheets(M).Name)
Then
WB.Worksheets(N).Move before:=WB.Worksheets(M)
End If
End If
Next N
Next M

SortWorksheetsByName = True

End Function



Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]