Sorting sheets in Workbook
 

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

Sorting sheets in Workbook
 

Hi
see your other post

P.S.: please don't multipost

--
Regards
Frank Kabel
Frankfurt, Germany

"Mnj 78" schrieb im Newsbeitrag
...
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

Sorting sheets in Workbook
 

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

--
halem
-----------------------------------------------------------------------
halem2's Profile: http://www.excelforum.com/member.php...nfo&userid=993
View this thread: http://www.excelforum.com/showthread.php?threadid=26854