Worksheets numerically sorting

[kulen_dbraco]

Dear VBAExpress friends,
this is my very first post on this forum... I really admire Your knowledge and enthusiasm here... You're all great!!

I have a question that might be an easy-peasy for You...

I'm dealing with worksheets in excel that are named by numbers (eg. 2.9, 2.10, 2.93) and I want to sort them numerically.
But I'm a bit confused by the decimal places that the names have, and I'm not sure how to write the right code.

When I sort them (with the macro that I'm using), names like, for example, 2.9 appears after 2.85 (which is kinda right),
but my 2.9 should be before it..... You can take a look on the photos attached.....You'll get it there Thank You!


At the moment I'm using this code:

Sub WSsort()
Application.ScreenUpdating = False
Dim ShCount As Integer, i As Integer, j As Integer
ShCount = Sheets.Count
For i = 1 To ShCount - 1
    For j = i + 1 To ShCount
        If UCase(Sheets(j).Name) < UCase(Sheets(i).Name) Then
            Sheets(j).Move before:=Sheets(i)
        End If
    Next j
Next i
Application.ScreenUpdating = True
End Sub

Random.jpgTocno.jpgKAOPoslozeno.jpg

[kulen_dbraco]

I found out that KUTOOLS does the thing that I need...
He manage to sort the worksheets in a Alpha-Numeric order... (take a look on the photo attached).

But, is there a way to see the code that runs behind this action? So I can integrate it in my own code?

Screenshot_1.jpg

Thank You!

[Aussiebear]

Not unless the programmer behind Kutools wants to share it with you.

[p45cal]

Try blah (which call the function below it):

Sub blah()
ReDim ShtNames(1 To Sheets.Count)
'get the sheet names:
For i = 1 To Worksheets.Count
  ShtNames(i) = Worksheets(i).Name
Next i
'sort the sheet names:
For j = 1 To UBound(ShtNames) - 1
  For i = 1 To UBound(ShtNames) - 1
    If Not aLessThanb(ShtNames(i), ShtNames(i + 1)) Then
      temp = ShtNames(i)
      ShtNames(i) = ShtNames(i + 1)
      ShtNames(i + 1) = temp
    End If
  Next i
Next j
'Move the sheets:
For Each nm In ShtNames
  Sheets(nm).Move after:=Sheets(Sheets.Count)
Next nm
End Sub


Function aLessThanb(a, b) As Boolean
x = Split(CStr(a), ".")
y = Split(CStr(b), ".")
For i = 0 To Application.Min(UBound(x), UBound(y))
  Z = Application.Max(Len(x(i)), Len(y(i)))
  x(i) = Format(x(i), String(Z, "0"))
  y(i) = Format(y(i), String(Z, "0"))
Next i
aLessThanb = Join(x) < Join(y)
End Function

[Paul_Hossler]

1. Do your ws names really have a decimal point at the end also, e.g. 2.10<point> because that's the way in looks in your screen shots??

2. Why do you want 2.7. and 2.9. to go before 2.10. ? If they were 2.07 and 2.09 I could see it

[p45cal]

As I see it, the OP has the full stops/periods/decimal points as delimiters rather than as decimal points, so whatever is between them is a whole number.
This is like the numbering of Bills of Material, or paragraph numbering in legal documents.