Here's a quirky little problem. I know most people are more concerned
with sorting sheet names in alphabetical order, but I have some code
that generates a lot of data and automatically adds a new worksheet to
put
some of the data on when it needs it. I start with a one-sheet
workbook
and may end up with 30 or more sheets. The new sheets are
automatically
named Sheet1, Sheet2, etc, of course, (and I just leave them that
way).
For some reason, the new sheets seem to "come in" from the left so
that
the tabs are ordered numerically from right to left. That is the
Sheet1
tab is on the far right and the highest numbered tab is to the left.
This makes it harder to work with them and I also have code that
requires
them to be in normal numerical order. So, I used the bit of code below
which was provided by David M. Higgs, thank you very much. It turned
the
order around the way it should be (for the most part) but there was a
little problem. The result was 1,10,11,12,13,14,15,16,17,18,19,2,20,
etc
which is the same order in which they appear in the Project Explorer,
and is obviously an alphabetical sort order. All I had to do was move
a few sheets (2,3,4,5,6,7,8, & 9), but I was wondering if it was
possible to get it right with an actual numerical sort.

Sub SortSheets()

Dim i, j As Integer
Dim iNumSheets As Integer

iNumSheets = ActiveWorkbook.Sheets.Count

Application.ScreenUpdating = False
For i = 1 To iNumSheets - 1
For j = i + 1 To iNumSheets
If Sheets(i).Name Sheets(j).Name Then
Sheets(j).Move befo=Sheets(i)
End If
Next j
Next i
Application.ScreenUpdating = True

End Sub

Hi John,

You are almost there now. I am assuming that all sheets are named Sheet1,
Sheet2,...Sheet33 etc.

.....


Sub SortSheets()

Dim i, j As Integer
Dim iNumSheets As Integer
Dim sString As String

iNumSheets = ActiveWorkbook.Sheets.Count

Application.ScreenUpdating = False
For i = 1 To iNumSheets - 1
For j = i + 1 To iNumSheets
If Val(Mid(Sheets(i).Name, 6)) Val(Mid(Sheets(j).Name, 6))
Then
Sheets(j).Move befo=Sheets(i)
End If
Next j
Next i
Application.ScreenUpdating = True

End Sub

Hope that helps.

Regards,
Kevin



"John Pierce" wrote in message
om...
Here's a quirky little problem. I know most people are more concerned
with sorting sheet names in alphabetical order, but I have some code
that generates a lot of data and automatically adds a new worksheet to
put
some of the data on when it needs it. I start with a one-sheet
workbook
and may end up with 30 or more sheets. The new sheets are
automatically
named Sheet1, Sheet2, etc, of course, (and I just leave them that
way).
For some reason, the new sheets seem to "come in" from the left so
that
the tabs are ordered numerically from right to left. That is the
Sheet1
tab is on the far right and the highest numbered tab is to the left.
This makes it harder to work with them and I also have code that
requires
them to be in normal numerical order. So, I used the bit of code below
which was provided by David M. Higgs, thank you very much. It turned
the
order around the way it should be (for the most part) but there was a
little problem. The result was 1,10,11,12,13,14,15,16,17,18,19,2,20,
etc
which is the same order in which they appear in the Project Explorer,
and is obviously an alphabetical sort order. All I had to do was move
a few sheets (2,3,4,5,6,7,8, & 9), but I was wondering if it was
possible to get it right with an actual numerical sort.

Sub SortSheets()

Dim i, j As Integer
Dim iNumSheets As Integer

iNumSheets = ActiveWorkbook.Sheets.Count

Application.ScreenUpdating = False
For i = 1 To iNumSheets - 1
For j = i + 1 To iNumSheets
If Sheets(i).Name Sheets(j).Name Then
Sheets(j).Move befo=Sheets(i)
End If
Next j
Next i
Application.ScreenUpdating = True

End Sub

You have to watch the line wrap...

The following should be on one line with a space...hopefully this will show
correctly.

If Val(Mid(Sheets(i).Name, 6)) Val(Mid(Sheets(j).Name, 6)) Then

You should be on your way.



"Kevin Stecyk" wrote in message
...
Hi John,

You are almost there now. I am assuming that all sheets are named
Sheet1,
Sheet2,...Sheet33 etc.

....


Sub SortSheets()

Dim i, j As Integer
Dim iNumSheets As Integer
Dim sString As String

iNumSheets = ActiveWorkbook.Sheets.Count

Application.ScreenUpdating = False
For i = 1 To iNumSheets - 1
For j = i + 1 To iNumSheets
If Val(Mid(Sheets(i).Name, 6)) Val(Mid(Sheets(j).Name, 6))
Then
Sheets(j).Move befo=Sheets(i)
End If
Next j
Next i
Application.ScreenUpdating = True

End Sub

Hope that helps.

Regards,
Kevin



"John Pierce" wrote in message
om...
Here's a quirky little problem. I know most people are more concerned
with sorting sheet names in alphabetical order, but I have some code
that generates a lot of data and automatically adds a new worksheet to
put
some of the data on when it needs it. I start with a one-sheet
workbook
and may end up with 30 or more sheets. The new sheets are
automatically
named Sheet1, Sheet2, etc, of course, (and I just leave them that
way).
For some reason, the new sheets seem to "come in" from the left so
that
the tabs are ordered numerically from right to left. That is the
Sheet1
tab is on the far right and the highest numbered tab is to the left.
This makes it harder to work with them and I also have code that
requires
them to be in normal numerical order. So, I used the bit of code below
which was provided by David M. Higgs, thank you very much. It turned
the
order around the way it should be (for the most part) but there was a
little problem. The result was 1,10,11,12,13,14,15,16,17,18,19,2,20,
etc
which is the same order in which they appear in the Project Explorer,
and is obviously an alphabetical sort order. All I had to do was move
a few sheets (2,3,4,5,6,7,8, & 9), but I was wondering if it was
possible to get it right with an actual numerical sort.

Sub SortSheets()

Dim i, j As Integer
Dim iNumSheets As Integer

iNumSheets = ActiveWorkbook.Sheets.Count

Application.ScreenUpdating = False
For i = 1 To iNumSheets - 1
For j = i + 1 To iNumSheets
If Sheets(i).Name Sheets(j).Name Then
Sheets(j).Move befo=Sheets(i)
End If
Next j
Next i
Application.ScreenUpdating = True

End Sub

Kevin,
Thanks for wading through my overly long question. Your solution was
elegant and effective. Thanks again.
John

John,

My pleasure.

Regards,
Kevin


"John Pierce" wrote in message
om...
Kevin,
Thanks for wading through my overly long question. Your solution was
elegant and effective. Thanks again.
John