sorting worksheets by time

  • Thread starter Thread starter davez
  • Start date Start date
D

davez

Hi all, my first time here & I am hoping someone can help with th
following -

I have a workbook I use daily which can contain between 10 to 2
worksheets. The worksheets are named 1,2,3 & so on & each has a tim
asigned to it in the format "h:mm", which is located at cell C1.

What I am looking for is a way to sort the worksheets by their tim
value, earliest to latest.

Thanks in advance for any help provided
 
Chip Pearson has some code to sort by worksheet name at:
http://cpearson.com/excel/sortws.htm

Option Explicit

Sub SortWorksheets()

Dim N As Integer
Dim M As Integer
Dim FirstWSToSort As Integer
Dim LastWSToSort As Integer
Dim SortDescending As Boolean

SortDescending = False

If ActiveWindow.SelectedSheets.Count = 1 Then
FirstWSToSort = 1
LastWSToSort = Worksheets.Count
Else
With ActiveWindow.SelectedSheets
For N = 2 To .Count
If .Item(N - 1).Index <> .Item(N).Index - 1 Then
MsgBox "You cannot sort non-adjacent sheets"
Exit Sub
End If
Next N
FirstWSToSort = .Item(1).Index
LastWSToSort = .Item(.Count).Index
End With
End If

For M = FirstWSToSort To LastWSToSort
For N = M To LastWSToSort
If SortDescending = True Then
If Worksheets(N).Range("C1").Value _
Worksheets(M).Range("c1").Value Then
Click to expand...
Worksheets(N).Move Before:=Worksheets(M)
End If
Else
If Worksheets(N).Range("c1").Value _
< Worksheets(M).Range("C1").Value Then
Worksheets(N).Move Before:=Worksheets(M)
End If
End If
Next N
Next M

End Sub

Chip's code is pretty neat.

You can sort just the selected sheets (click on the first and ctrlclick on
subsequent--but they have to be adjacent).

And you can sort ascending or descending depending on that flag near the top:

SortDescending = False

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Back
Top