Sorting sheets in Workbook

[Guest]

Hi,
I have a workbook which has around 450 sheets and unordered. All the sheets
are named using integer strings viz..1, 2, 5, 6, 11, 56 etc. New sheets will
be added at the end.
I want to order these sheets using Excel macro or VBA.
Could you please let me know how I could achieve this?

Requirement is
Current order of workbook sheets
1, 5, 3, 7, 10,.9,15, 20,22, 2 etc.

After running the macro, I need the sheets to be in the order
1,2,3,5,7,9,10,15,20,22 etc.

Thanks in Advance,
Mnj 78

 

[Frank Kabel]

Hi
see your other post

P.S.: please don't multipost

 

[halem2]

There's a book called Writing Excel Macros by Steven Roman, which ha
exactly what you need. This is his code for sorting:

Sub SortALLSheets()
'
Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range
Dim cSheets As Integer
Dim sSheets() As String
Dim i As Integer
'
Set wb = ActiveWorkbook 'get true array dimension
cSheets = wb.Sheets.Count
ReDim sSheets(1 To cSheets) 'fill array with worksheet names
'
For i = 1 To cSheets
sSheets(i) = wb.Sheets(i).Name
Next
Set ws = wb.Worksheets.Add 'create new sheet and put names in firs
column
'
For i = 1 To cSheets
ws.Cells(i, 1).Value = sSheets(i)
Next
'
ws.Columns(1).Sort Key1:=ws.Columns(1), Order1:=xlAscending 'sor
column
'
For i = 1 To cSheets 'refill array
sSheets(i) = ws.Cells(i, 1).Value
Next
'
Application.DisplayAlerts = False
ws.Delete 'delete previously added sheet
Application.DisplayAlerts = True
'
For i = 1 To cSheets 'reorder sheets by moving each one to the end
wb.Sheets(sSheets(i)).Move After:=wb.Sheets(cSheets)
Next
'
Sheets(1).Activate
End Su