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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Worksheets Numerically
does this version of cp's code sort correctly?
Sub AlphaSortWorksheets() Dim N As Integer Dim M As Integer Dim FirstWSToSort As Integer Dim LastWSToSort As Integer Dim SortDescending As Boolean SortDescending = False FirstWSToSort = 1 LastWSToSort = Worksheets.Count For M = FirstWSToSort To LastWSToSort For N = M To LastWSToSort If SortDescending = True Then If UCase(Worksheets(N).Name) _ UCase(Worksheets(M).Name) Then Worksheets(N).Move befo=Worksheets(M) End If Else If UCase(Worksheets(N).Name) < _ UCase(Worksheets(M).Name) Then Worksheets(N).Move befo=Worksheets(M) End If End If Next N Next M End Sub -- Gary "jnf40" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Worksheets Numerically
No, I had the AlphaSort code in first.
"Gary Keramidas" wrote: does this version of cp's code sort correctly? Sub AlphaSortWorksheets() Dim N As Integer Dim M As Integer Dim FirstWSToSort As Integer Dim LastWSToSort As Integer Dim SortDescending As Boolean SortDescending = False FirstWSToSort = 1 LastWSToSort = Worksheets.Count For M = FirstWSToSort To LastWSToSort For N = M To LastWSToSort If SortDescending = True Then If UCase(Worksheets(N).Name) _ UCase(Worksheets(M).Name) Then Worksheets(N).Move befo=Worksheets(M) End If Else If UCase(Worksheets(N).Name) < _ UCase(Worksheets(M).Name) Then Worksheets(N).Move befo=Worksheets(M) End If End If Next N Next M End Sub -- Gary "jnf40" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Worksheets Numerically
You can try the free Excel add-in "Excel Extras".
By default It sorts sheets in true numerical order. (It can also use the standard MS sort order where 10 comes before 2.) It does other nice stuff like creating a linked Table of Contents sheet. Download from ... http://www.realezsites.com/bus/primitivesoftware No registration required. -- Jim Cone San Francisco, USA .. "jnf40" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Worksheets Numerically
Jim...I have never used add-ins, I don't guess. If I were to add this on my
computer, when I send the workbook to others would they also need this add-in on their computer in order for it to work? If this is so then it will not work. "Jim Cone" wrote: You can try the free Excel add-in "Excel Extras". By default It sorts sheets in true numerical order. (It can also use the standard MS sort order where 10 comes before 2.) It does other nice stuff like creating a linked Table of Contents sheet. Download from ... http://www.realezsites.com/bus/primitivesoftware No registration required. -- Jim Cone San Francisco, USA .. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Worksheets Numerically
i really need it to sort by code due to the way worksheets are added.
"jnf40" wrote: Jim...I have never used add-ins, I don't guess. If I were to add this on my computer, when I send the workbook to others would they also need this add-in on their computer in order for it to work? If this is so then it will not work. "Jim Cone" wrote: You can try the free Excel add-in "Excel Extras". By default It sorts sheets in true numerical order. (It can also use the standard MS sort order where 10 comes before 2.) It does other nice stuff like creating a linked Table of Contents sheet. Download from ... http://www.realezsites.com/bus/primitivesoftware No registration required. -- Jim Cone San Francisco, USA .. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Worksheets Numerically
Yes, to use an add-in it must be installed on each computer that
needs it. Most add-ins install very easily... Tools | Add-ins and checkmark the add-in name (or browse to find it). If you provide code in your workbook, then other users will have to OK the use of macros when they open the workbook. You will also have to provide some way to run the code... Either a button on the worksheet or an attached toolbar. -- Jim Cone San Francisco, USA http://www.officeletter.com/blink/specialsort.html "jnf40" wrote in message Jim...I have never used add-ins, I don't guess. If I were to add this on my computer, when I send the workbook to others would they also need this add-in on their computer in order for it to work? If this is so then it will not work. "Jim Cone" wrote: You can try the free Excel add-in "Excel Extras". By default It sorts sheets in true numerical order. (It can also use the standard MS sort order where 10 comes before 2.) It does other nice stuff like creating a linked Table of Contents sheet. Download from ... http://www.realezsites.com/bus/primitivesoftware No registration required. -- Jim Cone San Francisco, USA .. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Worksheets Numerically
in your example, 005, 010, 015, what order are they supposed to be in after
they're sorted? -- Gary "jnf40" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Worksheets Numerically
They will be in that order...005, 010, 015, but if the user forgot one and
added 007, as the code is now it would be 005, 010, 015, 007...I need it to be 005, 007, 010, 015.. After the sheets are created and named the code automatically goes to the sort sub. "Gary Keramidas" wrote: in your example, 005, 010, 015, what order are they supposed to be in after they're sorted? -- Gary "jnf40" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Worksheets Numerically
i added 007 after 015 so the order was 005,010,015,007.
when i ran the code i posted, the sheets were in this order: 005,007,010,015 so, i guess i don't see what you do or i am missing something -- Gary "jnf40" wrote in message ... They will be in that order...005, 010, 015, but if the user forgot one and added 007, as the code is now it would be 005, 010, 015, 007...I need it to be 005, 007, 010, 015.. After the sheets are created and named the code automatically goes to the sort sub. "Gary Keramidas" wrote: in your example, 005, 010, 015, what order are they supposed to be in after they're sorted? -- Gary "jnf40" wrote in message ... 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Worksheets Numerically
well I'll try it again and let you know.
"Gary Keramidas" wrote: i added 007 after 015 so the order was 005,010,015,007. when i ran the code i posted, the sheets were in this order: 005,007,010,015 so, i guess i don't see what you do or i am missing something -- Gary "jnf40" wrote in message ... They will be in that order...005, 010, 015, but if the user forgot one and added 007, as the code is now it would be 005, 010, 015, 007...I need it to be 005, 007, 010, 015.. After the sheets are created and named the code automatically goes to the sort sub. "Gary Keramidas" wrote: in your example, 005, 010, 015, what order are they supposed to be in after they're sorted? -- Gary "jnf40" wrote in message ... 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 |
Reply |
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 |