AnsweredAssumed Answered

Sorting Sheet Names

Question asked by Matt Bieringer on Oct 10, 2017
Latest reply on Oct 10, 2017 by Josh Brady

Hello all,

I currently have a macro that will create a drawing from a part or assembly. It creates a new sheet for each configuration. My issue comes when I try to reorder the sheets via a bubblesort I get some odd results.

The code:

'Sorts Sheets 
Dim vSheetArr As Variant
vSheetArr = swDraw.GetSheetNames
For i = 0 To SheetCount - 1
    sheetName = vSheetArr(i)
    If sheetName Like "*OP*" Then
        sheetName = Right(sheetName, Len(sheetName) - 3)
        vSheetArr(i) = CLng(sheetName)
    End If
Next i
Call Sort(vSheetArr)
For i = 0 To SheetCount - 1
    sheetName = vSheetArr(i)
    If IsNumeric(sheetName) Then
        sheetName = "OP " & sheetName
        vSheetArr(i) = sheetName
    End If
Next I
bool = swDraw.ReorderSheets(vSheetArr)
If bool = False Then
    MsgBox "NO WORKIE"
    Unload Me
End If


Sub Sort(vSheetArr As Variant)      'Sort Sheets
Dim first           As Long
Dim last            As Long
Dim i               As Long
Dim j               As Long
Dim Temp            As Long
Dim list            As Long
    first = LBound(vSheetArr)
    last = UBound(vSheetArr)
    For i = first To last - 1
        For j = i + 1 To last
            If vSheetArr(i) > vSheetArr(j) Then
                Temp = vSheetArr(j)
                vSheetArr(j) = vSheetArr(i)
                vSheetArr(i) = Temp
            End If
        Next j
    Next i
End Sub


So if I have configurations OP 1, OP 2, OP 3, OP 10, & OP 100. The sort will output this as: OP 1, OP 10, OP 100, OP 2, & OP 3.


Any help would be appreciated.