How to arrange Worksheets using Macros?

[Ephraim]

hi,

I have created and saved number of worksheets in Excel and would like them to be arranged in a properly specifed manner - ascending or descending order. I want to ask how can I arrange these Excel worksheets ? Is it possible to do this using macros....kindly suggest

[Eric B]

Here's the macro code for you -

Sub Sort_Worksheets() 

Dim i As Integer
Dim j As Integer
Dim iAnswer As VbMsgBoxResult

   iAnswer = MsgBox("Sort Sheets in Ascending Order?" & Chr(10) _
     & "Clicking No will sort in Descending Order", _
     vbYesNoCancel + vbQuestion + vbDefaultButton1, "Sort Worksheets")
   For i = 1 To Sheets.Count
      For j = 1 To Sheets.Count - 1

         If iAnswer = vbYes Then
            If UCase$(Sheets(j).Name) > UCase$(Sheets(j + 1).Name) Then
               Sheets(j).Move After:=Sheets(j + 1)
            End If

         ElseIf iAnswer = vbNo Then
            If UCase$(Sheets(j).Name) < UCase$(Sheets(j + 1).Name) Then
               Sheets(j).Move After:=Sheets(j + 1)

            End If
         End If

      Next j
   Next i

End Sub

[Eric B]

The above macro will sort all of the worksheets in the current workbook. It can sort in ascending or descending order. This type of sort is conducted through a bubble sort type.

This is ideal when you are working with a large number of tabs or worksheets in Excel documents and you need to put them in order.

[Glenny]

Excel does not provide a mechanism or command to sort or otherwise order worksheets within a workbook. There are number of VBA functions that you can use to sort or reorder worksheets. With these functions, you can -

• Sort some or all worksheet by name, in either ascending or descending order.
• Order the worksheets by the names provided in an array.
• Order the worksheets in either ascending or descending order based on a cell reference in each worksheet.
• Group worksheet by tab color (Excel 2002 and later only).
• Order worksheets based on sheet names in a range of cells.

You can download a module file containing all the required code and functions for sorting all the Worksheet tabs in a Excel workbook.

[Thamesvalley79]

Here's the macro code for you -

Sub Sort_Worksheets() 

Dim i As Integer
Dim j As Integer
Dim iAnswer As VbMsgBoxResult

   iAnswer = MsgBox("Sort Sheets in Ascending Order?" & Chr(10) _
     & "Clicking No will sort in Descending Order", _
     vbYesNoCancel + vbQuestion + vbDefaultButton1, "Sort Worksheets")
   For i = 1 To Sheets.Count
      For j = 1 To Sheets.Count - 1

         If iAnswer = vbYes Then
            If UCase$(Sheets(j).Name) > UCase$(Sheets(j + 1).Name) Then
               Sheets(j).Move After:=Sheets(j + 1)
            End If

         ElseIf iAnswer = vbNo Then
            If UCase$(Sheets(j).Name) < UCase$(Sheets(j + 1).Name) Then
               Sheets(j).Move After:=Sheets(j + 1)

            End If
         End If

      Next j
   Next i

End Sub

The code above works great, but what change do I need to make to the code if I want the sheets to sort in the following order 1, 2, 3, ... 10, 11, 12,... 100, 101, 102 followed by any sheets with letters such as W51, W52, W53

Any help would be greatly appreciated.

Thanks in advance!

Robert

[Macarenas]

If you want better information on how you can sort your sheet in nubers order is by going on the net ther you will find more information if you do not understand the above coding. You can also visit the microsoft official site for your quiry for better result. Re-arranging worksheet with macros is not a big deal.