sort by cofename

[KDS14589]

I have the following code that works fine………but

I've been trying to sort it by code name but everything I try it sorts by the index numbers, I WANT IT SORTED BY CODENAME, any help appreciated

I included a picture of what it's doing

  '''''Restricted worksheets list'''''
    
    Cells.Range("K5:N104").ClearContents
    
    Const SwitchBoardNameG As String = "general.misc"
    Const FilterCellG As String = "J5"
    Const OutputRowG As Long = 6
    Const IndexClmG As String = "M"
    Const NameClmG As String = "L"
    Const VisibleClmG As String = "N"
    Const CodeNameClmG As String = "K"
    
    Dim SbG As Worksheet
    Dim FltG As String
    Dim TabNamesG() As String
    Dim rG As Long
    Dim WsG As Worksheet
    Dim RngG As Range
    
        Set SbG = ThisWorkbook.Worksheets(SwitchBoardNameG)
        FltG = SbG.Range(FilterCellG).Cells(1).Value
        ReDim TabNamesG(ThisWorkbook.Worksheets.Count)
        
    rG = OutputRow
    [L4] = [{"Name"}]
    [K4] = [{"CodeName"}]
    [M4] = [{"Index"}]
    [N4] = [{"Visibility"}]
        
        For Each WsG In ThisWorkbook.Worksheets
        If (WsG.CodeName Like (Range("L3"))) Then
        If InStr(1, WsG.CodeName, FltG, vbTextCompare) = 1 Then
        SbG.Cells(rG, CodeNameClmG).Resize(, 4).Value = Array(WsG.CodeName, WsG.Name, WsG.Index, WsG.Visible)
        rG = rG + 1
        End If
        End If
        Next WsG
        
        If rG Then
        Set RngG = SbG.Range(SbG.Cells(OutputRowG, CodeNameClmG), SbG.Cells(rG - 1, CodeNameClmG))
        With Sb.Sort
        With .SortFields
        .Clear
        .Add Key:=RngG.Cells(1), _
        SortOn:=xlSortOnValues, _
        Order:=xlDescending, _
        DataOption:=xlSortTextAsNumbers
        End With
        .SetRange RngG
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    End If

 

[Michael M]

Have you considered extracting part of the string in the codename to sort by ?
In a helper column using =MID(A2,3,2)....then sort by that column !

 

[Gokhan Aycan]

I can't see any reference to "sb" in the code other than "With Sb.Sort".

This works for me (DataOption commented out, Add key set to columns(1), no header in my test).

Private Sub test_sort()
    
    Dim rng As Range
    
    Set rng = ActiveSheet.Range("A1").CurrentRegion
    With ActiveSheet.Sort
        With .SortFields
            .Clear
            .Add Key:=rng.Columns(1), _
            SortOn:=xlSortOnValues, _
            Order:=xlDescending ', DataOption:=xlSortTextAsNumbers
        End With
        .SetRange rng
        .Header = xlNo
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
End Sub

Before sort order to the right.

 

[johnnyL]

@KDS14589 Are you just wanting a sorted list of the code names of sheets?

Sub CodeNameSorter()
'
    Dim CodeNameArray   As Object
    Dim CodeNameCounter As Long
'
    Set CodeNameArray = CreateObject("System.Collections.ArrayList")
'
    With ThisWorkbook
        For CodeNameCounter = 1 To .Worksheets.Count
             CodeNameArray.Add .Worksheets(CodeNameCounter).CodeName                                ' Load the Code Names into an array
        Next
'
        CodeNameArray.Sort                                                                          ' Sort the Code Names
'
        For CodeNameCounter = 0 To CodeNameArray.Count - 1
            Sheets("Sheet1").Range("F" & CodeNameCounter + 5) = CodeNameArray(CodeNameCounter)      ' Display the sorted Code Names to "Sheet1" F5
        Next
    End With
End Sub

That code will sort the Code Names and print to the F column.

 

[KDS14589]

I can't see any reference to "sb" in the code other than "With Sb.Sort".

This works for me (DataOption commented out, Add key set to columns(1), no header in my test).

Private Sub test_sort()
   
    Dim rng As Range
   
    Set rng = ActiveSheet.Range("A1").CurrentRegion
    With ActiveSheet.Sort
        With .SortFields
            .Clear
            .Add Key:=rng.Columns(1), _
            SortOn:=xlSortOnValues, _
            Order:=xlDescending ', DataOption:=xlSortTextAsNumbers
        End With
        .SetRange rng
        .Header = xlNo
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
   
End Sub

Before sort order to the right.
View attachment 45731

Sorry it took so-long. But I had to do some 'refinements'. ? such as the part of code that said

Order:=xlDescending ', DataOption:=xlSortTextAsNumbers I had to change to xlascending but now it works

THANKS