Graham

With the sheets in the original order you want.........

To get the sheets listed in comma delimited order in F2 on Sheet1.......

Sub Comma_Delimit22()
Dim x As Range
Dim y As Range
Dim z As Range
Dim w As String
Dim Rng As Range
Dim i As Long
Dim Sh As Worksheet
Dim sbuf As String

Application.DisplayAlerts = False
With Sheets.Add
.Name = "ListSheet"
Set Rng = Range("A1")
End With

For Each Sh In ActiveWorkbook.Sheets
If Sh.Name < "ListSheet" Then
Rng.Offset(i, 0).Value = Sh.Name
i = i + 1
End If
Next Sh

w = ","
Set z = Sheets("Sheet1").Range("F2")
Set x = Sheets("ListSheet").Range("A1", ActiveCell.End(xlDown))
For Each y In x
If Len(y.Text) 0 Then sbuf = sbuf & y.Text & w
Next
z = Left(sbuf, Len(sbuf) - 1)
Sheets("ListSheet").Delete
Application.DisplayAlerts = True

End Sub

Move your sheets around as you are wont to do.

To re-sort them in the original order run Bob's macro.


Gord Dibben Excel MVP

On Mon, 21 Feb 2005 04:15:02 -0800, Graham
wrote:

Thanks for your reply, Bob.
The worksheet name dosn't help at all, how would I know whether they were a
comma delimited list? The only useful value to sort by is the numeric value
in Cell F2 on each work sheet ( e.g. 21345), to be sorted in ascending order.
Sheet 1 = Lowest value number to Sheet 50+ =Highest value number. Once you
have this code where do you enter it, and how do you run it?

"Bob Phillips" wrote:

Assuming the list of sheet names is a comma delimited list
(Sheet1,Sheet2,...) in sheet1 F2, then this code does it

Sub SortSheets()
Dim arySheets
Dim i As Long

arySheets = Split(Worksheets("Sheet1").Range("F2"), ",")
For i = LBound(arySheets) To UBound(arySheets)
Worksheets(arySheets(i)).Move after:=Worksheets(Worksheets.Count)
Next i

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Graham" wrote in message
...
I have a large workbook of some 50 worksheets. I need to be able to keep
them
in a specific order, based on a value in Cell F2.
By necessity I have to alter the order of the sheets, and then have to
return them to their correct order manually (Assuming I have saved changes
whilst moved out of position) . Is there a simple way of either setting a
default sheet order and/or returning the sheets to the specific order,
based
on the value in F2 ?
I understand that it can be done in VBA code - but I havn't a clue how to
do
this. Any advice would be much appreciated.