Maintaining a default worksheet order
 

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.

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.

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.

There is a fundamental problem with using the sheet number, as soon as you
move one, the numbers of the rest change, so you cannot rely on that. Why do
you need to know if they were a comma delimited list, what exactly does that
mean? I assumed that you would enter the sheet names in such a comma
delimited list.

Just put the code in a standard code module. You can then run it from the
macros list (ToolsMacroMacros).

--

HTH

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


"Graham" wrote in message
...
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.

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.