Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort worksheet tabs based on ASCII values.
I am writing a macro in Excel and I have a workbook that has worksheets with
multiple tabs. (An example of the tab names is: VFD11, VFD2, VFD1, VFM42, VFM2). Each tab has a three letter prefix (ex. VFD, VFM) and I have to sort worksheets with the same prefix by ASCII. I have already written code that groups the tabs with the same prefix together in the workbook, now I need to sort those individual groups by ASCII values and re-order the worksheets. I believe I need to use nested for loops but am having trouble figuring out the structure/logic of the for loops. Could anyone help me with this? If you need more explanation please ask, it was difficult for me to explain this via text. Thank you in advance! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort worksheet tabs based on ASCII values.
see if this helps:
http://support.microsoft.com/kb/812386/ "BVinson" wrote: I am writing a macro in Excel and I have a workbook that has worksheets with multiple tabs. (An example of the tab names is: VFD11, VFD2, VFD1, VFM42, VFM2). Each tab has a three letter prefix (ex. VFD, VFM) and I have to sort worksheets with the same prefix by ASCII. I have already written code that groups the tabs with the same prefix together in the workbook, now I need to sort those individual groups by ASCII values and re-order the worksheets. I believe I need to use nested for loops but am having trouble figuring out the structure/logic of the for loops. Could anyone help me with this? If you need more explanation please ask, it was difficult for me to explain this via text. Thank you in advance! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort worksheet tabs based on ASCII values.
JLGWhiz - Thanks but I have already looked there and it doesn't work for my
workbook. I think the reason why it doesn't work is because (for example) if there was VFD2 and VFD12 in the worksheets, it seems to compare them string character by string character and then stops once it hits 2 and 1 because it says "1 is less than 2" and re-orders it. Basically, it stops as soon as it finds a difference and does not compare the whole number portion..that's why I was trying to use the ascii values to sort the groups... Thank you though for giving me the link... "JLGWhiz" wrote: see if this helps: http://support.microsoft.com/kb/812386/ "BVinson" wrote: I am writing a macro in Excel and I have a workbook that has worksheets with multiple tabs. (An example of the tab names is: VFD11, VFD2, VFD1, VFM42, VFM2). Each tab has a three letter prefix (ex. VFD, VFM) and I have to sort worksheets with the same prefix by ASCII. I have already written code that groups the tabs with the same prefix together in the workbook, now I need to sort those individual groups by ASCII values and re-order the worksheets. I believe I need to use nested for loops but am having trouble figuring out the structure/logic of the for loops. Could anyone help me with this? If you need more explanation please ask, it was difficult for me to explain this via text. Thank you in advance! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort worksheet tabs based on ASCII values.
Yes, it does a digital sort. I am not smart enough to tell you how to change
the code to make a numerical evaluation of a portion of the sheet name, however, if the numbers used a zero fill to the left (VFD001 = VFD1, VFD011 = VFD11, etc.) then it would sort like you want. "BVinson" wrote: JLGWhiz - Thanks but I have already looked there and it doesn't work for my workbook. I think the reason why it doesn't work is because (for example) if there was VFD2 and VFD12 in the worksheets, it seems to compare them string character by string character and then stops once it hits 2 and 1 because it says "1 is less than 2" and re-orders it. Basically, it stops as soon as it finds a difference and does not compare the whole number portion..that's why I was trying to use the ascii values to sort the groups... Thank you though for giving me the link... "JLGWhiz" wrote: see if this helps: http://support.microsoft.com/kb/812386/ "BVinson" wrote: I am writing a macro in Excel and I have a workbook that has worksheets with multiple tabs. (An example of the tab names is: VFD11, VFD2, VFD1, VFM42, VFM2). Each tab has a three letter prefix (ex. VFD, VFM) and I have to sort worksheets with the same prefix by ASCII. I have already written code that groups the tabs with the same prefix together in the workbook, now I need to sort those individual groups by ASCII values and re-order the worksheets. I believe I need to use nested for loops but am having trouble figuring out the structure/logic of the for loops. Could anyone help me with this? If you need more explanation please ask, it was difficult for me to explain this via text. Thank you in advance! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort worksheet tabs based on ASCII values.
I tried to figure out how to get it to work for me and I couldn't figure it
out.....I also can't use zero filled sheet tabs. :*( Here is the code I used to group the tabs by 3 letter prefix if this helps anyone: Dim beginSheet As String Dim nextSheet As String Max = ThisWorkbook.Worksheets.Count ' finds the maximum array size 'Remove these lines Dim firstGroup As Integer Dim secondGroup As Integer Dim thirdGroup As Integer Dim fourthGroup As Integer Dim moved As Boolean moved = False Dim categoryNo As Integer categoryNo = 1 n = 1 For n = 1 To Max x = n + 1 For t = x To (Max) beginSheet = Worksheets(n).Name beginSheet = Left(beginSheet, 3) nextSheet = Worksheets(t).Name nextSheet = Left(nextSheet, 3) If (beginSheet = nextSheet) Then Worksheets(t).Move after:=Worksheets(n) moved = True End If Next t If (moved = False) Then If (categoryNo = 1) Then firstGroup = n categoryNo = categoryNo + 1 ElseIf (categoryNo = 2) Then secondGroup = n categoryNo = categoryNo + 1 ElseIf (categoryNo = 3) Then thirdGroup = n categoryNo = categoryNo + 1 ElseIf (categoryNo = 4) Then fourthGroup = n End If End If moved = False Next n |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort worksheet tabs based on ASCII values.
Oops disregard the "remove these lines comments" I haven't taken them out of
the code yet. "BVinson" wrote: I tried to figure out how to get it to work for me and I couldn't figure it out.....I also can't use zero filled sheet tabs. :*( Here is the code I used to group the tabs by 3 letter prefix if this helps anyone: Dim beginSheet As String Dim nextSheet As String Max = ThisWorkbook.Worksheets.Count ' finds the maximum array size 'Remove these lines Dim firstGroup As Integer Dim secondGroup As Integer Dim thirdGroup As Integer Dim fourthGroup As Integer Dim moved As Boolean moved = False Dim categoryNo As Integer categoryNo = 1 n = 1 For n = 1 To Max x = n + 1 For t = x To (Max) beginSheet = Worksheets(n).Name beginSheet = Left(beginSheet, 3) nextSheet = Worksheets(t).Name nextSheet = Left(nextSheet, 3) If (beginSheet = nextSheet) Then Worksheets(t).Move after:=Worksheets(n) moved = True End If Next t If (moved = False) Then If (categoryNo = 1) Then firstGroup = n categoryNo = categoryNo + 1 ElseIf (categoryNo = 2) Then secondGroup = n categoryNo = categoryNo + 1 ElseIf (categoryNo = 3) Then thirdGroup = n categoryNo = categoryNo + 1 ElseIf (categoryNo = 4) Then fourthGroup = n End If End If moved = False Next n |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort worksheet tabs based on ASCII values.
I am going to try to explain a little better here what I am trying to do....
As an example my worksheet has: |VFD11 | VFD2| VFD1 | VFM45 | VFM43 | VFM2 | ( "|" indicates new tab) 1 2 3 4 5 6 So my firstGroup integer would be 3 (it stopped sorting the VFD worksheets on tab 3). My secondGroup integer would be 6. Now I need to sort the first section/group which are the tabs VFD11 through VFD1, in ascending order. So I know I need to compare 2 sheets at a time, from 1 to firstGroup (3)...but to do it enough times to sort all of the worksheets...so I believe that is (n-1) times.... But to compare the sheets I have to have another for loop to loop through and get the total ascii values for the first worksheet and the one I am comparing it to...then I have to compare the ascii values and if the first sheets' value is than the one I am comparing, swap them.... I have to do this for each section.... Hope this helped to clarify my problem..... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I name Worksheet Tabs based on some king of reference? | Excel Worksheet Functions | |||
Sort Worksheet Tabs | Excel Programming | |||
Exporting to ASCII Txt Doc without Tabs | Excel Discussion (Misc queries) | |||
Sort Worksheet Tabs | Excel Programming | |||
Re-Sort Worksheet tabs | Excel Worksheet Functions |