PDA

View Full Version : Solved: How to sort worksheets?



m.azrie
12-19-2006, 06:21 PM
Hello?:hi:
any shortcut code to sorting multiple sheets in workbooks.?:dunno

Bob Phillips
12-19-2006, 06:56 PM
No, you need code.

m.azrie
12-19-2006, 07:04 PM
is it..

any simple code easy to understand?..

lucas
12-19-2006, 07:33 PM
I use this one. Its pretty easy to understand:
Sub SortWorksheets()
' sort worksheets in a workbook in ascending order
Dim sCount As Integer, i As Integer, j As Integer
Application.ScreenUpdating = False
sCount = Worksheets.Count
If sCount = 1 Then Exit Sub
For i = 1 To sCount - 1
For j = i + 1 To sCount
If Worksheets(j).Name < Worksheets(i).Name Then
Worksheets(j).Move Before:=Worksheets(i)
End If
Next j
Next i
End Sub

m.azrie
12-19-2006, 07:43 PM
Thank you lucas, i tried and succesful.
but my sheet contain numbering 1 to 26, corse sorting incorrectly,
sheets like : 1, 10, 11, 12, ..... 2, 20, 21, .....
it should 1,2,3,4 ...... 26.

JimmyTheHand
12-19-2006, 09:22 PM
Is there a pattern in how you name the sheets? (E.g. each sheet is called "Reportx" where x is a positive integer, or in each name there is a "-" before the number, etc.)

m.azrie
12-19-2006, 09:34 PM
Yes, like : 1-ABC 06, 2-DEF 06, 3-HIJ 06,... 25-XXX 06, 26-YYY 06.

JimmyTheHand
12-19-2006, 11:15 PM
Then Steve's code should be modified this way:
Sub SortWorksheets()
' sort worksheets in a workbook in ascending order
Dim sCount As Integer, i As Integer, j As Integer
Application.ScreenUpdating = False
sCount = Worksheets.Count
If sCount = 1 Then Exit Sub
For i = 1 To sCount - 1
For j = i + 1 To sCount
If Val(Worksheets(j).Name) < Val(Worksheets(i).Name) Then '<--- here's the change
Worksheets(j).Move Before:=Worksheets(i)
End If
Next j
Next i
End Sub

maytas
12-29-2006, 04:58 PM
Hi.
Also you can try this:
Sub Sort_Sheets()
Dim i As Integer, j As Integer
For i = 1 To Worksheets.Count
For j = i + 1 To Worksheets.Count
If StrComp(Worksheets(j).Name, Worksheets(i).Name, vbTextCompare) = -1 Then
Worksheets(j).Move before:=Worksheets(i)
End If
Next j
Next i
End Sub