Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Order for Worksheets
I have a file with a bunch of worksheets for tracking data on people. I would
like to sort the placements of those sheets (not the entire workbook, as there are non-people related tabs in there that I want to stay). I have a tab with the sort order I want for those worksheets. Is there a way to place these worksheets in the same order as the list I got? Ideally, I would be doing this in a UserForm (but I bet, if it can be done, the code can fit anywhere) when I create a new person. TIA -- I am running on Office 2003, unless otherwise stated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Order for Worksheets
The following code takes a list of worksheet names (in the desired
order) and move them to the appropriate positions. Sheets that are not included in the list cells are not moved. You can specify before which worksheet the moved sheets should appear. For example, you could have two initial sheets before the sorted sheets. Sub SortWSFromNames() Dim R As Range Dim N As Long Dim WS As Worksheet ' This references the list containing the ' sheet names in order. Sheets not named ' in this list are not moved. Set R = Worksheets("Sheet6").Range("A1") '<<<< CHANGE ' Set N to the sheet number before which ' the sorted sheets should be placed. To ' put the sorted sheets at the far left, ' use N = 1. Otherwise, set N to the sheet ' that should appear to the left of the first ' sorted sheet. N = 1 Do Until R.Value = vbNullString Set WS = Worksheets(R.Text) WS.Move befo=Worksheets(N) N = N + 1 Set R = R(2, 1) Loop End Sub Change the line marked with '<<< to the first cell of the list of worksheet names. The names should appear in a column, say A1:A5, and the code will terminate when a blank cell is encountered. Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Tue, 12 Jan 2010 06:11:01 -0800, Orion Cochrane wrote: I have a file with a bunch of worksheets for tracking data on people. I would like to sort the placements of those sheets (not the entire workbook, as there are non-people related tabs in there that I want to stay). I have a tab with the sort order I want for those worksheets. Is there a way to place these worksheets in the same order as the list I got? Ideally, I would be doing this in a UserForm (but I bet, if it can be done, the code can fit anywhere) when I create a new person. TIA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating New Worksheets & Sorting Order of Worksheets | Excel Programming | |||
Sorting the order of sheets by the name | Excel Programming | |||
sorting in order | Excel Worksheet Functions | |||
sorting in order | Excel Worksheet Functions | |||
Sorting Worksheets in Alphabetical Order (Left to Right) | Excel Programming |