Sort tabs numerically

[JStiehl]

I need help with VBA code to sort tab names with numbers in order, but leave
the ones with words as they are. Tab names with numbers should precede the
tabs with words. I have searched and can only find codes to sort
alphanumerically. Thanks for your help.

Example of what order I would like my tabs in:

123890
456678
789123
Project1
Project2
Project3
Total
Findings

 

[Don Guillett]

http://support.microsoft.com/kb/812386

 

[Bob Phillips]

See http://www.cpearson.com/excel/sortws.aspx

 

[Jacob Skaria]

Try

Sub Macro()
Dim lngCount1 As Long, lngCount2 As Long
For lngCount1 = 1 To Sheets.Count - 1
For lngCount2 = lngCount1 + 1 To Sheets.Count
If IsNumeric(Sheets(lngCount1).Name) And _
IsNumeric(Sheets(lngCount2).Name) Then
If CCur(Sheets(lngCount1).Name) > CCur(Sheets(lngCount2).Name) Then _
Sheets(lngCount1).Move After:=Sheets(lngCount2): Exit For
End If
Next
Next
End Sub

 

[Jacob Skaria]

Try this instead..

Sub Macro()
Dim lngCount1 As Long, lngCount2 As Long
For lngCount1 = 1 To Sheets.Count
For lngCount2 = lngCount1 + 1 To Sheets.Count
If Sheets(lngCount1).Name > Sheets(lngCount2).Name Then _
Sheets(lngCount1).Move After:=Sheets(lngCount2): Exit For
Next
Next
End Sub

 

[JStiehl]

Thanks Jacob. I tried both of the codes, but neither one sorted the tabs
with numbers in the proper order.

I tried the following code that I found, and it will sort the tabs with
numbers in the correct order, but put them at the end of all my tabs. I
changed the > symbol and it will put the tabs with numbers at the beginning,
but will sort them in descending order. I am not very skilled at VBA--is
there anything you can suggest to tweak this code to make it sort tabs with
numbers in ascending order and put them at the beginning of the tabs?

Thanks so much, I really appreciate your help.

Sub WorksheetsSortAscending()
Dim sCount As Integer, i As Integer, j As Integer
sCount = Worksheets.Count
If sCount = 1 Then Exit Sub
For i = 1 To sCount - 1
For j = i + 1 To sCount
If Val(Worksheets(j).Name) > Val(Worksheets(i).Name) Then
Worksheets(j).Move before:=Worksheets(i)
End If
Next j
Next i
End Sub

 

[Don Guillett]

Did you try the link I sent?

 

[JStiehl]

Hi Don,
Yes, I did try the link you sent. The problem with having it sort
alphanumerically is that it sorts the tabs with words in a different order
than what I need (Project1, Project2, Project3, Total, Findings). I'm
looking for a way to sort just the tabs with numbers, but leave the ones with
words as is. Also, it sorts the project tabs in a funky order--Project3,
Project30, Project31, etc. (I didn't mention in my original post that the
project tabs could go up that high). Is there anything I can do to modify
that code to work as I would like it to? Thanks for your time and help.

Example of what order I would like my tabs in:

123890
456678
789123
Project1
Project2
Project3
Total
Findings

 

[Don Guillett]

Send your raw file to my email below. If desired, strip all but sheet names.

 

[Don Guillett]

This could probably be refined to make an array>sort the array and move the
sheets, but it works to move the sorted numbered sheets to the front ....

Sub SAS_SortOnlyNumberedSheetsToFront()
Sheets.Add before:=Sheets(1)
r = 1
For i = 1 To Sheets.Count
If IsNumeric(Sheets(i).Name) Then
Cells(r, 1) = Sheets(i).Name
r = r + 1
End If
Next i
Columns(1).Sort Key1:=Range("A1"), Order1:=xlAscending
p = 0
For Each c In ActiveSheet.UsedRange
'MsgBox CStr(c)
Sheets(CStr(c)).Move After:=Sheets(p + 1)
p = p + 1
Next c
'deletes dummy sheet
Application.DisplayAlerts = False
Sheets(1).Delete
Application.DisplayAlerts = True
End Sub