Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Worksheets Numerically
I have tried Chip Pearson's sorting code but it doesn't work on my
worksheets. I think his code has to have something more than just numbers. I have the worksheet names formatted as follows... mylinenum = Range("linenum") If Range("linenum").Value < 1000 Then mylinenum = Format(mylinenum, "000") ElseIf Range("linenum").Value 999 Then mylinenum = Format(mylinenum, "0000") End If this works fine to name the worksheets 005, 010, 015 etc. But it will not sort the worksheets. Can this be done using numbers only for the worksheet names after I have formatted the worksheet names this way? The following is the sort code... Sub SortTheSheets() 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 CInt(Mid(Worksheets(N).Name, 6)) _ CInt(Mid(Worksheets(M).Name, 6)) Then Worksheets(N).Move Befo=Worksheets(M) End If Else If CInt(Mid(Worksheets(N).Name, 6)) < _ CInt(Mid(Worksheets(M).Name, 6)) Then Worksheets(N).Move Befo=Worksheets(M) End If End If Next N Next M End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting rows left to right numerically | Excel Worksheet Functions | |||
Sorting Numerically | Excel Worksheet Functions | |||
how do i sort a column numerically going from 01-01 to 225-99 | Excel Worksheet Functions | |||
Columns showing numerically | Excel Discussion (Misc queries) | |||
Sort Numerically Worksheets via VB when creating a new worksheets | Excel Programming |