Ordering of sheets in a workbook
 

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

Ordering of sheets in a workbook
 

T;341931 Wrote:
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 befo=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)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=95642

Ordering of sheets in a workbook
 

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
Befo=Sheets(i)
End If
Next j
Next i
Application.ScreenUpdating = True

End Sub

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

"Simon Lloyd" wrote:


T;341931 Wrote:
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 befo=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)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=95642

Ordering of sheets in a workbook
 

Hey Simon,

I tried it again, and its working.

Cheers.



"T" wrote:

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
Befo=Sheets(i)
End If
Next j
Next i
Application.ScreenUpdating = True

End Sub

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

"Simon Lloyd" wrote:


T;341931 Wrote:
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 befo=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)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=95642

Ordering of sheets in a workbook
 

No problem.....although you did have me puzzled :)T;341964 Wrote:
Hey Simon,

I tried it again, and its working.

Cheers.



"T" wrote:

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
Befo=Sheets(i)
End If
Next j
Next i
Application.ScreenUpdating = True

End Sub

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

"Simon Lloyd" wrote:


T;341931 Wrote:
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 befo=Sheets(i)
End If
Next j
Next i
Application.ScreenUpdating = True
End Sub

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


--
Simon Lloyd

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

------------------------------------------------------------------------
Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon
Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread: 'Ordering of sheets in a workbook - The Code Cage
Forums' (http://www.thecodecage.com/forumz/sh...ad.php?t=95642)




--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=95642