none
Excel VBA code for ordering worksheets using sheet code name RRS feed

  • Question

  • Hi All,

    I have workbook with multiple sheets having sheet code name combination (wstemp & integer) like wstemp4, wstemp2, wstemp3, wstemp1 and so on. Is it possible to order the sheets with sheet code name's integer in ascending order like wstemp1,wstemp2, wstemp3 and so on. Integer in code name of multiple sheets will not be greater than sheets.count.

    Also i have 2 worksheets having sheet code name as wsmain and wsfinal which shall be placed at the end.

    Below code will order the sheets based on sheet name and not sheet code name

    SheetsCounter = Sheets.Count
    
     
    
    For i = 2 To SheetsCounter
    
    For n = 1 To SheetsCounter
    
    If Sheets(n).Name > Sheets(i).Name Then
    
    Sheets(i).Move before:=Sheets(n)
    
    End If
    
    Next n
    
    Next i

    • Edited by zaveri cc Wednesday, December 30, 2015 9:58 PM
    Wednesday, December 30, 2015 9:54 PM

Answers

  • Hi, zaveri cc
    According to your description, you could refer to below code:

    Sub SortTest()
       'Debug.Print Worksheets("wstemp2").Index
       Worksheets("wsmain").Move Before:=Worksheets(1)
       Worksheets("wsfinal").Move After:=Worksheets(Worksheets.Count)
       
       Dim strNum
       Dim endNum
       strNum = 2
       endNum = Worksheets.Count - 1
       
       For i = strNum To endNum
          For j = strNum To endNum - 1
            If UCase$(Worksheets(j).CodeName) > UCase$(Worksheets(j + 1).CodeName) Then
                   Worksheets(j).Move After:=Worksheets(j + 1)
                End If
    
          Next j
       Next i
    End Sub
    

    For more information, click here to refer about How to sort worksheets alphanumerically in a workbook in Excel

    • Marked as answer by zaveri cc Saturday, January 2, 2016 3:57 PM
    Friday, January 1, 2016 5:34 AM

All replies

  • >>>Also i have 2 worksheets having sheet code name as wsmain and wsfinal which shall be placed at the end.

    Below code will order the sheets based on sheet name and not sheet code name<<<

    According to your description, I suggest that you could use Worksheet.CodeName Property to get the code name for the object. This example displays the code name for worksheet one.

    Debug.Print Worksheets(1).CodeName

    For more information, click here to refer about Worksheet.CodeName Property (Excel)

    Thursday, December 31, 2015 1:48 AM
  • Hi David,

    My workbook is created dynamically on fly and i don't want the static way to debug and print the codename of worksheets. I want the code logic to order sheets by taking the end integer of code name and than ordering sheets in ascending order. For e.g i have sheets with codename wstemp4, wstemp2, wstemp3 which shall be order like wstemp2, 3 and 4.

    Thursday, December 31, 2015 2:27 AM
  • Try this and adapt as to suit

    Sub test()
    Dim i As Long, j As Long, n As Long, x As Long
    Dim wss() As Worksheet
    Dim suffix() As Long, idx() As Long
    Dim ws As Worksheet
    Const cPrefix As String = "wstemp"  ' << adapt
    
        x = Len(cPrefix) + 1
    
        ReDim wss(1 To Worksheets.Count)
        ReDim suffix(1 To UBound(wss)) As Long
        ReDim idx(1 To UBound(wss)) As Long
    
        For Each ws In Worksheets
            i = i + 1
            Set wss(i) = ws
            If ws.CodeName = "wsmain" Then
                suffix(i) = 0
            ElseIf ws.CodeName = "wsfinal" Then
                suffix(i) = 123456
            Else
                suffix(i) = CLng(Mid$(ws.CodeName, x, 3))
            End If
            idx(i) = i
        Next
    
        For i = 2 To UBound(suffix)  ' this sort normally better as a function
            For j = 1 To UBound(idx) - 1
                If suffix(idx(i)) < suffix(idx(j)) Then
                    n = idx(i)
                    idx(i) = idx(j)
                    idx(j) = n
                End If
            Next
        Next
    
        For i = 1 To UBound(suffix) - 1
            If Not wss(idx(i)) Is Worksheets(i) Then
                wss(idx(i)).Move Before:=Worksheets(i)
            End If
        Next
    End Sub

    Assumes apart from the given first and last codenames all codenames have the same given length prefix followed by a numeric suffix.

    Note, newly added sheets which have never seen the VBE will not have been assigned a codename, just an empty string (can be forced though).

    Try and work out how the code works

    Thursday, December 31, 2015 11:56 AM
    Moderator
  • Below line gives run time error 12- type mismatch error

    suffix(i) = CLng(Mid$(ws.CodeName, x, 3))

    Thursday, December 31, 2015 2:14 PM
  • Just saying there's an error is not helpful.

    What are the values of the variables ws.Codename and x 

    Thursday, December 31, 2015 4:19 PM
    Moderator
  • Hi, zaveri cc
    According to your description, you could refer to below code:

    Sub SortTest()
       'Debug.Print Worksheets("wstemp2").Index
       Worksheets("wsmain").Move Before:=Worksheets(1)
       Worksheets("wsfinal").Move After:=Worksheets(Worksheets.Count)
       
       Dim strNum
       Dim endNum
       strNum = 2
       endNum = Worksheets.Count - 1
       
       For i = strNum To endNum
          For j = strNum To endNum - 1
            If UCase$(Worksheets(j).CodeName) > UCase$(Worksheets(j + 1).CodeName) Then
                   Worksheets(j).Move After:=Worksheets(j + 1)
                End If
    
          Next j
       Next i
    End Sub
    

    For more information, click here to refer about How to sort worksheets alphanumerically in a workbook in Excel

    • Marked as answer by zaveri cc Saturday, January 2, 2016 3:57 PM
    Friday, January 1, 2016 5:34 AM
  • Thanks David, with little tweak code works fine.
    Saturday, January 2, 2016 3:57 PM
  • If you need to sort ten or more sheets have another look at what I posted for you to avoid (*1,*10,*11,*2,*3). If not David's "text" sort should be fine, assuming the first and last sheets are to referenced by sheetname rather than codename as I had understood.
    Sunday, January 3, 2016 11:59 AM
    Moderator