Ordering of sheets in a workbook

[T]

Is it possible to order worksheets in a workbook in alpha order according to
the tab name? The alternative I have is to copy and move each one and it will
take me a very long time for the said workbook.

thanks

 

[Simon Lloyd]

Is it possible to order worksheets in a workbook in alpha order
according to
the tab name? The alternative I have is to copy and move each one and
it will
take me a very long time for the said workbook.

thanksHow about running this code, it goes in a standard module:

*How to add and run a Macro*1. *Copy* the macro above pressing
the keys *CTRL+C*
2. Open your workbook
3. Press the keys *ALT+F11* to open the Visual Basic Editor
4. Press the keys *ALT+I* to activate the *Insert menu*
5. *Press M* to insert a *Standard Module*
6. *Paste* the code by pressing the keys *CTRL+V*
7. Make any custom changes to the macro if needed at this time.
8. *Save the Macro* by pressing the keys *CTRL+S*
9. Press the keys *ALT+Q* to exit the Editor, and return to Excel.

*To Run the Macro...*
To run the macro from Excel, open the workbook, and press *ALT+F8* to
display the *Run Macro Dialog*. Double Click the macro's name to *Run*
it.



Code:
--------------------
Sub Sort_Tabs()
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 UCase(Sheets(i).Name) > UCase(Sheets(j).Name) Then
Sheets(j).Move before:=Sheets(i)
End If
Next j
Next i
Application.ScreenUpdating = True
End Sub

--------------------


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[T]

Hey Simon,

Thanks for sending through the code. I don't use VB often, but I've tried to
break down the code to what is shown below. I however get a compile error at
"Sheets(i)..." I'm not sure how to correct this.

~~~~~~~~~~~~~~~~~~~~
Sub Sort_Tabs()

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 UCase(Sheets(i).Name) > UCase(Sheets(j).Name) Then Sheets(j).Move
Before:=Sheets(i)
End If
Next j
Next i
Application.ScreenUpdating = True

End Sub

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[T]

Hey Simon,

I tried it again, and its working.

Cheers.

 

[Simon Lloyd]

Hey Simon,

I tried it again, and its working.

Cheers.

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)