Special VBA for sorting tabs

[cb123]

Hi all i have a macro for sorting tabs as below but i have number of tabs which are in the 9 thousands and 10 thousands and it doesn't recognise the 10000 as higher than the 9000

Example a tab named 10001 is lower than tab named 9388 and i need to address this. Does anyone have a work around for this situation? TIA.

Sub Sort_Active_Book()
Dim i As Integer
Dim j As Integer
Dim iAnswer As VbMsgBoxResult
'
' Prompt the user as which direction they wish to
' sort the worksheets.
'
iAnswer = MsgBox("Sort Sheets in Ascending Order?" & Chr(10) _
& "Clicking No will sort in Descending Order", _
vbYesNoCancel + vbQuestion + vbDefaultButton1, "Sort Worksheets")
For i = 1 To Sheets.Count
For j = 1 To Sheets.Count - 1
'
' If the answer is Yes, then sort in ascending order.
'
If iAnswer = vbYes Then
If UCase$(Sheets(j).Name) > UCase$(Sheets(j + 1).Name) Then
Sheets(j).Move After:=Sheets(j + 1)
End If
'
' If the answer is No, then sort in descending order.
'
ElseIf iAnswer = vbNo Then
If UCase$(Sheets(j).Name) < UCase$(Sheets(j + 1).Name) Then
Sheets(j).Move After:=Sheets(j + 1)
End If
End If
Next j
Next i
End Sub

 

[steve the fish]

It will be seeing the names as text even if it is a numeric. If text 10000 is less than 9000 because 1 is less than 9. You could wrap the sheet name with Val eg

Val(Sheets(j).Name)

if the sheet names are always numeric

 

[cb123]

It will be seeing the names as text even if it is a numeric. If text 10000 is less than 9000 because 1 is less than 9. You could wrap the sheet name with Val eg

Val(Sheets(j).Name)

if the sheet names are always numeric

Thanks for this that's really helpful. I am quite inexperienced can you tell me exactly how i do that please?

 

[steve the fish]

You do it where you are testing the sheet names:

If UCase$(Sheets(j).Name) > UCase$(Sheets(j + 1).Name) Then

becomes:

If Val(Sheets(j).Name) > Val(Sheets(j + 1).Name) Then

 

[cb123]

You do it where you are testing the sheet names:

If UCase$(Sheets(j).Name) > UCase$(Sheets(j + 1).Name) Then

becomes:

If Val(Sheets(j).Name) > Val(Sheets(j + 1).Name) Then

Thanks but my knowledge really is basic i do not know step by step how to accomplish this. If you can elaborate in more detail at all it would be much appreciated. Go here, do this etc.

 

[steve the fish]

Where did you get that code from? You must know or you cant have pasted it here. Go back there and where you see the lines like I described with the greater than symbol change the lines from using UCase to Val.

 

[shg]

Try this:

Sub SortSheets(Optional wkb As Workbook = Nothing, _
               Optional ByVal iBeg As Long = 1, _
               Optional ByVal iEnd As Long = 2147483647)
  ' shg 2009-09
  ' Insertion-sorts sheets from iBeg to iEnd
  ' sorts sheet names that look like numbers as numbers

  Dim i             As Long
  Dim j             As Long
  Dim vShti         As Variant
  Dim vShtj         As Variant

  If wkb Is Nothing Then Set wkb = ActiveWorkbook

  With wkb
    If iBeg < 1 Then iBeg = 1
    If iEnd > .Sheets.Count Then iEnd = .Sheets.Count

    For i = iBeg + 1 To iEnd
      vShti = LCase(Sheets(i).Name)
      If IsNumeric(vShti) Then vShti = CDbl(vShti)
      
      For j = iBeg To i - 1
        vShtj = LCase(Sheets(j).Name)
        If IsNumeric(vShtj) Then vShtj = CDbl(vShtj)

         If vShti < vShtj Then
          .Sheets(i).Move Before:=.Sheets(j)
          Exit For
        End If
      Next j
    Next i
  End With
End Sub