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!

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!

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!

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!

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

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