Sort just the sheet tabs with numbers on them.

Hello!

I found shg's code and I am trying to adapt it to my workbook.

Each workbook has a sheet named "Cover" and another labelled "TOC" and the other sheets have a mix of numbers and letters ("4500181 sn1", "4500181 sn2", "4500183", etc.).

I want to leave the "Cover" and "TOC" and then sort the other sheets that have number (or number/letter) names.

Here's the code I am working with:

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

  Dim i           As Long
  Dim j           As Long

  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
          For j = iBeg To i - 1
          If Sheets(i).Name = "Cover" Then
          ' do nothing
          Else
              If StrComp(.Sheets(i).Name, .Sheets(j).Name, vbTextCompare) <> 1 Then
                  .Sheets(i).Move Before:=.Sheets(j)
                  Exit For
              End If
              End If
          Next j
      Next i
  End With
End Sub

Anyone have any ideas?

Respectfully,

Lost

MarvinP,

Workbook attached.

The Cover and TOC always wind up at the right rather than the left.

??

Lost

MarvinP,

The code gets caught in a crazy loop and you have to CTRL-ALT-DELETE it to stop it. I think it is continually sorting the sheets.



Lost

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

  Dim i           As Long
  Dim j           As Long

  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
          For j = iBeg To i - 1
          If Sheets(i).Name = "Cover" Or Sheets(i).Name = "TOC" Then
          ' do nothing
          Else
              If StrComp(.Sheets(i).Name, .Sheets(j).Name, vbTextCompare) <> 1 Then
                  .Sheets(i).Move Before:=.Sheets(j)
                  Exit For
              End If
              End If
          Next j
      Next i
  End With
  Sheets("TOC").Move Before:=Sheets(1)
  Sheets("Cover").Move Before:=Sheets(1)
End Sub

MarvinP,

1. Workbook with 4 tabs in this order:

1000, 101, Cover, TOC

2. Run the code.

3. It sorts them like this:

Cover, TOC, 1000, 101

I am Googling to see if I can find better code.

Lost

MarvinP,

I found one that seems to work great!

Sub WorksheetsSortAscending()
'http://www.erlandsendata.no/english/vba/wb/sortsheets.htm
' sort worksheets in a workbook in ascending order
Dim sCount As Integer, i As Integer, j As Integer
Application.ScreenUpdating = False

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
If Worksheets(j).name = "Cover" Or Worksheets(j).name = "TOC" Then
'do nothing
Else
Worksheets(j).Move Before:=Worksheets(i)
End If
End If
Next j
Next i
Application.ScreenUpdating = True
End Sub

Thanks for your help with this thread!

Respectfully,

Lost