Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Sheets (Need to create 500 individual sheets in one workbook, pulling DATA | Excel Worksheet Functions | |||
How to repeat a code for selected sheets (or a contiguous range of sheets) in a Workbook? | Excel Worksheet Functions | |||
Sorting Sheets in a Workbook | Excel Programming | |||
Sorting Sheets in a workbook | Excel Programming | |||
Sorting Sheets in Workbook | Excel Programming |