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

 

[KDS14589]

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

Sorry for the late reply (medical issue, I retired several years ago so old age is catching up). I'm working with your code and tried changes to get the correct ascending list based by FULL codename, but no success. Your list is arranged by the first letter of the Codename but I need it based on the full 4 digits as seen in this image. I apologize if It may seem I'm obsessed with Codename, It's because my other code experiments are with codename instead of worksheet name.

 

[Fluff]

Did you try the code I suggested in post#4?

 

[KDS14589]

Did you try the code I suggested in post#4?

I tried it but nothing was listed even with your last code. Your suggestions in the past worked so it was baffling not this time. Sorry if I did something wrong.

 

[Fluff]

Did you get any errors?

 

[KDS14589]

Did you get any errors?

no errors. no list. it's baffling. Sorry but at the time I had no energy or will to investigate, old age

 

[Skyybot]

Sorry for the late reply (medical issue, I retired several years ago so old age is catching up). I'm working with your code and tried changes to get the correct ascending list based by FULL codename, but no success. Your list is arranged by the first letter of the Codename but I need it based on the full 4 digits as seen in this image. I apologize if It may seem I'm obsessed with Codename, It's because my other code experiments are with codename instead of worksheet name.
View attachment 117258

Sorry. That was my mistake. Try this.

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.CodeName
    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