Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to sort worksheets
I have tried Chip Pearson's sorting codes and I can't get it to work on my
worksheet names, maybe it can't be done... My worksheet names are created by code...the names are as follows: 01-01-06 CSB 18" RCP; 01-01-06 CSB 18" RCP (2); 12-01-05 CSB 18" RCP after the sort I would like the order to be: 12-01-05 CSB 18" RCP; 01-01-06 CSB 18" RCP; 01-01-06 CSB 18" RCP (2)... Can this be done? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to sort worksheets
That sort magic is all in the comparison of the names to each other.
You will need to modify Pearson's code to do a different type of comparison Extract the first 8 characters from the name and convert that to a date. then compare the dates if they match, then compare the remainder. -- Regards, Tom Ogilvy "jnf40" wrote in message ... I have tried Chip Pearson's sorting codes and I can't get it to work on my worksheet names, maybe it can't be done... My worksheet names are created by code...the names are as follows: 01-01-06 CSB 18" RCP; 01-01-06 CSB 18" RCP (2); 12-01-05 CSB 18" RCP after the sort I would like the order to be: 12-01-05 CSB 18" RCP; 01-01-06 CSB 18" RCP; 01-01-06 CSB 18" RCP (2)... Can this be done? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to sort worksheets
Thanks for the response...you make it sound so easy, but I'm not that good
with vba to know where to begin with that. "Tom Ogilvy" wrote: That sort magic is all in the comparison of the names to each other. You will need to modify Pearson's code to do a different type of comparison Extract the first 8 characters from the name and convert that to a date. then compare the dates if they match, then compare the remainder. -- Regards, Tom Ogilvy "jnf40" wrote in message ... I have tried Chip Pearson's sorting codes and I can't get it to work on my worksheet names, maybe it can't be done... My worksheet names are created by code...the names are as follows: 01-01-06 CSB 18" RCP; 01-01-06 CSB 18" RCP (2); 12-01-05 CSB 18" RCP after the sort I would like the order to be: 12-01-05 CSB 18" RCP; 01-01-06 CSB 18" RCP; 01-01-06 CSB 18" RCP (2)... Can this be done? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to sort worksheets
This modification of Chip's code worked for me with your samples.
Sub SortWorksheets() Dim N As Integer Dim M As Integer Dim FirstWSToSort As Integer Dim LastWSToSort As Integer Dim SortDescending As Boolean Dim bGreater 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 sN = Worksheets(N).Name sM = Worksheets(M).Name dtN = CDate(Left(sN, 8)) dtM = CDate(Left(sM, 8)) sN1 = Right(sN, Len(sN) - 8) sM1 = Right(sM, Len(sM) - 8) If dtN dtM Then bGreater = True ElseIf dtN < dtM Then bGreater = False Else If StrComp(sN1, sM1, vbTextCompare) = 0 Then bGreater = True Else bGreater = False End If End If If SortDescending = True Then If bGreater Then Worksheets(N).Move Befo=Worksheets(M) End If Else If Not bGreater Then Worksheets(N).Move Befo=Worksheets(M) End If End If Next N Next M End Sub -- Regards, Tom Ogilvy "jnf40" wrote in message ... Thanks for the response...you make it sound so easy, but I'm not that good with vba to know where to begin with that. "Tom Ogilvy" wrote: That sort magic is all in the comparison of the names to each other. You will need to modify Pearson's code to do a different type of comparison Extract the first 8 characters from the name and convert that to a date. then compare the dates if they match, then compare the remainder. -- Regards, Tom Ogilvy "jnf40" wrote in message ... I have tried Chip Pearson's sorting codes and I can't get it to work on my worksheet names, maybe it can't be done... My worksheet names are created by code...the names are as follows: 01-01-06 CSB 18" RCP; 01-01-06 CSB 18" RCP (2); 12-01-05 CSB 18" RCP after the sort I would like the order to be: 12-01-05 CSB 18" RCP; 01-01-06 CSB 18" RCP; 01-01-06 CSB 18" RCP (2)... Can this be done? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to sort worksheets
Another option would be to rename your existing worksheets using a format like:
yyyy-mm-dd XXX ###" XXX You may want to make sure that the inches portion includes enough characters for the worse case scenario: So for example: 2006-01-28 CSB 001" RCP jnf40 wrote: I have tried Chip Pearson's sorting codes and I can't get it to work on my worksheet names, maybe it can't be done... My worksheet names are created by code...the names are as follows: 01-01-06 CSB 18" RCP; 01-01-06 CSB 18" RCP (2); 12-01-05 CSB 18" RCP after the sort I would like the order to be: 12-01-05 CSB 18" RCP; 01-01-06 CSB 18" RCP; 01-01-06 CSB 18" RCP (2)... Can this be done? -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to sort worksheets
Thank you both...Tom's worked fine...thanks again
"Dave Peterson" wrote: Another option would be to rename your existing worksheets using a format like: yyyy-mm-dd XXX ###" XXX You may want to make sure that the inches portion includes enough characters for the worse case scenario: So for example: 2006-01-28 CSB 001" RCP jnf40 wrote: I have tried Chip Pearson's sorting codes and I can't get it to work on my worksheet names, maybe it can't be done... My worksheet names are created by code...the names are as follows: 01-01-06 CSB 18" RCP; 01-01-06 CSB 18" RCP (2); 12-01-05 CSB 18" RCP after the sort I would like the order to be: 12-01-05 CSB 18" RCP; 01-01-06 CSB 18" RCP; 01-01-06 CSB 18" RCP (2)... Can this be done? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort Worksheets (without VB) | Excel Worksheet Functions | |||
Sort worksheets | Excel Programming | |||
Sort Worksheets | Excel Worksheet Functions | |||
Sort Numerically Worksheets via VB when creating a new worksheets | Excel Programming | |||
HELP sort 2 worksheets | Excel Programming |