macro to re-order sheets based on cell value?
 

I have 22 sheets with dientical layouts in a workbook. what i need is
macro fo some kind that will re-order the sheets in the workbook base
on the value in a particular cell (it will be the same cell in each o
the sheets).

Basicallly I have 3 sets of dates on these sheets and i need to be abl
to order the sheets in the workbook based on which one of these date
I need to sort by at the time (basically putting the earliest dates a
the front).

A macro that will sort the 22 sheets putting the earliest date in C1 a
the start would do. If i then need to order them by E1 instead the
I'll only have to change a tiny bit in the macro.

Thank

--
Message posted from http://www.ExcelForum.com

macro to re-order sheets based on cell value?
 

This Should help you


Sub arrange()
Dim ws As Worksheet
Dim cnt, cnt1
Application.ScreenUpdating = False

For cnt = 1 To Sheets.Count - 1
For cnt1 = cnt + 1 To Sheets.Count
'Change A1 your cell or cell name
If Sheets(cnt).Range("A1").Value Sheets(cnt1).Range("A1").Value Then
Sheets(cnt).Move After:=Sheets(cnt1)
End If
Next
Next

Application.ScreenUpdating = False
End Sub


neowok < wrote:

I have 22 sheets with dientical layouts in a workbook. what i need is a
macro fo some kind that will re-order the sheets in the workbook based
on the value in a particular cell (it will be the same cell in each of
the sheets).

Basicallly I have 3 sets of dates on these sheets and i need to be able
to order the sheets in the workbook based on which one of these dates
I need to sort by at the time (basically putting the earliest dates at
the front).

A macro that will sort the 22 sheets putting the earliest date in C1 at
the start would do. If i then need to order them by E1 instead then
I'll only have to change a tiny bit in the macro.

Thanks


---
Message posted from http://www.ExcelForum.com/

macro to re-order sheets based on cell value?
 

Here's some simple code

Dim i As Long
Dim fOrder As Boolean

Do
fOrder = True
For i = 1 To Worksheets.Count - 1
If Worksheets(i).Range("C1").Value _
Worksheets(i + 1).Range("C1").Value Then
Worksheets(i).Move after:=Worksheets(i + 1)
fOrder = False
End If
Next i
Loop Until fOrder



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"neowok " wrote in message
...
I have 22 sheets with dientical layouts in a workbook. what i need is a
macro fo some kind that will re-order the sheets in the workbook based
on the value in a particular cell (it will be the same cell in each of
the sheets).

Basicallly I have 3 sets of dates on these sheets and i need to be able
to order the sheets in the workbook based on which one of these dates
I need to sort by at the time (basically putting the earliest dates at
the front).

A macro that will sort the 22 sheets putting the earliest date in C1 at
the start would do. If i then need to order them by E1 instead then
I'll only have to change a tiny bit in the macro.

Thanks


---
Message posted from http://www.ExcelForum.com/