Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
worksheets in alphabetical order in office 2007
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
worksheets in alphabetical order in office 2007
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 Befo=Sheets(x) End If Next Next End Sub Mike "DJH84" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
worksheets in alphabetical order in office 2007
That has worked fine.
Thanks for your help "Mike H" wrote: 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 Befo=Sheets(x) End If Next Next End Sub Mike "DJH84" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
worksheets in alphabetical order in office 2007
Glad I could help and thanks for the feedback
"DJH84" wrote: That has worked fine. Thanks for your help "Mike H" wrote: 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 Befo=Sheets(x) End If Next Next End Sub Mike "DJH84" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
worksheets in alphabetical order in office 2007
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 befo=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 "DJH84" skrev i meddelelsen ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can i re-sort multiple worksheets into alphabetical order | Excel Discussion (Misc queries) | |||
How to put worksheets in Alphabetical order? | Excel Discussion (Misc queries) | |||
How do I put worksheets in alphabetical order | New Users to Excel | |||
How do I put worksheets in alphabetical order | Excel Worksheet Functions | |||
how do i sort excel worksheets by alphabetical order? | Excel Worksheet Functions |