list sorted by ws.codename but shows ws.name in list

[KDS14589]

I would list ALL MY CODE ATTEMPTS but not enough space.

I’m trying to get a list of all worksheets in a workbook, but it would be in ascending order by worksheet.codename but only show a list of the worksheet.name in worksheet GE03.RANGE(“AH5:AH” & LDR_EH)
The examples I've seen actually alphabetically rearrange the worksheets, but I just want a list in GE03.RANGE(“AH5:AH” & LDR_EH).

worksheet.codename’s are 4 charters; two letters followed by two numbers ie GE03

The code I’m using for just a random list is…..

Dim Ws As Worksheet

Dim x As Integer

Dim wbk As Workbook

Dim wbkName As String



x = 5

wbkName = ThisWorkbook.Name

Set wbk = Application.Workbooks(wbkName)

For Each Ws In wbk.Worksheets

GE03.Cells(x, 34) = Ws.Name

Next Ws

 

[Fluff]

How about

Sub KDS()
   Dim Ary As Variant
   Dim Ws As Worksheet
   Dim i As Long
  
   With ThisWorkbook
      ReDim Ary(1 To .Sheets.Count, 1 To 1)
      For Each Ws In .Worksheets
         i = i + 1
         Ary(i, 1) = Ws.CodeName
      Next Ws
   End With
   GE03.Range("AH5").Resize(UBound(Ary)).Value = Ary
End Sub

 

[Skyybot]

Does this work?

Option Explicit
Option Base 1

Sub atoz_Worksheet_List()
Dim wb As Workbook, ws As Worksheet, wshts(), x As Integer, i As Integer
Set wb = ThisWorkbook: x = 5
ReDim wshts(wb.Worksheets.Count)
i = 1
For Each ws In wb.Worksheets
    wshts(i) = ws.Name
    i = i + 1
Next ws
SortArrayAtoZ (wshts)
For i = 1 To UBound(wshts)
    GE03.Cells(x, 34) = wshts(i)
    x = x + 1
Next i
End Sub

Function SortArrayAtoZ(myArray As Variant)

Dim i As Long
Dim j As Long
Dim Temp

'Sort the Array A-Z
For i = LBound(myArray) To UBound(myArray) - 1
    For j = i + 1 To UBound(myArray)
        If UCase(myArray(i)) > UCase(myArray(j)) Then
            Temp = myArray(j)
            myArray(j) = myArray(i)
            myArray(i) = Temp
        End If
    Next j
Next i

SortArrayAtoZ = myArray

End Function

 

[Fluff]

Forgot to sort the list

Sub KDS()
   Dim Ary As Object
   Dim Ws As Worksheet
     
   Set Ary = CreateObject("System.Collections.ArrayList")
   With ThisWorkbook
      For Each Ws In .Worksheets
         Ary.Add Ws.CodeName
      Next Ws
   End With
   Ary.Sort
   GE03.Range("AH5").Resize(Ary.Count).Value = Application.Transpose(Ary.toarray)
End Sub