worksheets in alphabetical order in office 2007

[DJH84]

How do I arrange my worksheets in a single work book into alphabetical order.
I am using Vista & office 2007. If I need to use Macros I also need
instructions on what to do as I have never used them before.

 

[Mike H]

Hi,

I don't believe the method of entering code in Excel 2007 has changed but
someone will correct me if I'm wrong.

Right click any worksheet tab, view code and paste the code below in. With
the cursor within the code tap F5 in VB editor to run it and your sheets
should sort

Sub Sortem()
For x = 1 To Worksheets.Count
For y = x To Worksheets.Count
If UCase(Sheets(y).Name) < UCase(Sheets(x).Name) Then
Sheets(y).Move Before:=Sheets(x)
End If
Next
Next
End Sub


Mike

 

[DJH84]

That has worked fine.

Thanks for your help

Hi,

I don't believe the method of entering code in Excel 2007 has changed but
someone will correct me if I'm wrong.

Right click any worksheet tab, view code and paste the code below in. With
the cursor within the code tap F5 in VB editor to run it and your sheets
should sort

Sub Sortem()
For x = 1 To Worksheets.Count
For y = x To Worksheets.Count
If UCase(Sheets(y).Name) < UCase(Sheets(x).Name) Then
Sheets(y).Move Before:=Sheets(x)
End If
Next
Next
End Sub


Mike

 

[Mike H]

Glad I could help and thanks for the feedback

That has worked fine.

Thanks for your help

 

[Per Jessen]

Hi

To automate this you need a macro.

Open the macro editor by pressing Alt+F11 or click Visual Basic on the
Developer tab. Goto "Insert" > Module. Paste the code below into the sheet
which appears.

Close the VBA editor and goto the Developer tab. Click on Macros and select
the macro and click run.

Remember to save the workbook as a Macro Enabled Workbook to keep the macro.


Sub ArrangeSheets()
Dim ws As Worksheet
Dim r As Long

Application.ScreenUpdating = False
Set ws = Sheets.Add

For Each sh In ThisWorkbook.Sheets
If sh.Name <> ws.Name Then
ws.Range("A1").Offset(r, 0) = sh.Name
r = r + 1
End If
Next

ws.Columns("A:A").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Sheets(ws.Range("A1").Value).Move before:=Sheets(1)
For sh = 1 To r - 1
Sheets(ws.Range("A1").Offset(sh, 0).Value).Move after:=Sheets(sh)
Next
Application.DisplayAlerts = False
ws.Delete
With Application
.DisplayAlerts = True
.ScreenUpdating = True
End With
End Sub

Regards,
Per