Special VBA for sorting tabs

cb123

New Member
Joined
Jul 1, 2016
Messages
34
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
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,319
Office Version
  1. 365
Platform
  1. Windows
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

Code:
Val(Sheets(j).Name)

if the sheet names are always numeric
 

cb123

New Member
Joined
Jul 1, 2016
Messages
34
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

Code:
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

Well-known Member
Joined
Oct 20, 2009
Messages
8,319
Office Version
  1. 365
Platform
  1. Windows
You do it where you are testing the sheet names:

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

becomes:

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

cb123

New Member
Joined
Jul 1, 2016
Messages
34

ADVERTISEMENT

You do it where you are testing the sheet names:

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

becomes:

Code:
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

Well-known Member
Joined
Oct 20, 2009
Messages
8,319
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
Try this:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,397
Messages
5,601,434
Members
414,450
Latest member
Cassy_sn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top