worksheets in alphabetical order in office 2007

  • Thread starter Thread starter DJH84
  • Start date Start date
D

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.
 
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
 
That has worked fine.

Thanks for your help

Mike H said:
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
Click to expand...
 
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
 
Back
Top