Sorting Order for Worksheets

[Orion Cochrane]

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

 

[Chip Pearson]

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 before:=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]