macro to re-order sheets based on cell value?

N

neowok

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
 
Y

yogendra joshi

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
 
B

Bob Phillips

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)
 
Top