Sort worksheet tabs based on ASCII values.

[BVinson]

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!

 

[JLGWhiz]

see if this helps:
http://support.microsoft.com/kb/812386/

 

[BVinson]

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]

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]

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

 

[BVinson]

Oops disregard the "remove these lines comments" I haven't taken them out of
the code yet.

 

[BVinson]

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.....

 

[BVinson]

Okay, so I tried (for probably the 3rd time) trying to use the linked
(JLGWhiz) code to get my sort to work, finally I thought I had it because
everything worked fine up until a certain point.

For example I had some tabs: ABC2, ABC40, ABC41, ABC42, ABC34 and ABC 43
(this is how they were sorted by my code).

The sort "did not work as my human brain logically intended" when it hit
tabs ABC34 and ABC43 because based upon ASCII total they are equal.

Below is the code I used to get the ASCII total, could anyone give me a hand
on how to handle this issue?

Thanks!

'******************Sort first section************************
'If (group1) Then

For i = 1 To firstGroup
For j = 1 To (firstGroup - 1)

string1 = Worksheets(j).Name
string2 = Worksheets(j + 1).Name

For w = 1 To Len(string1)
asciiATotal = asciiATotal + Asc(Mid(string1, w, 1))
Next w

For w = 1 To Len(string2)
asciiBTotal = asciiBTotal + Asc(Mid(string2, w, 1))
Next w

If (asciiATotal) > (asciiBTotal) Then
Sheets(j).Move after:=Sheets(j + 1)

End If
asciiATotal = 0
asciiBTotal = 0
Next j
Next i
'End If