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

 

[Skyybot]

One more edit.

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).Name
    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]

@Skyybot I would suggest you test code, before posting it.

@KDS14589 If you try running my code using F8 to step through what happens?

 

[KDS14589]

@Skyybot I would suggest you test code, before posting it.

@KDS14589 If you try running my code using F8 to step through what happens?

On your post #4 I ran both F8 and my VERY PRIMITIVE attempts at debugging code but still get no lists.

I tried your code from #2 and it worked but as soon as you sorted (#4) it didn’t show anything, the same thing happens with the first code (#3) from Skyybo, I get an unsorted list, but in post #11 no list.

I apologize if it seems discourtesy to code from 2 sources but at this point, I’ll try anything.

 

[KDS14589]

One more edit.

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).Name
    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 no list now, your first code (#3) gave a list but now nothing.

 

[Fluff]

On your post #4 I ran both F8 and my VERY PRIMITIVE attempts at debugging code but still get no lists.

Are you sure you didn't get any error messages or warnings of any sort?

 

[KDS14589]

Are you sure you didn't get any error messages or warnings of any sort?

I reran #4 and now get Set Ary = CreateObject("System.Collections.ArrayList") as error
here is my full code, this gives me a chance to try several, i use which one works : yours is listed at end

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)
    AA04.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

Sub atoz_CodeName_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)
    AA04.Cells(x, 35) = wshts(i)
    x = x + 1
Next i
End Sub

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
   AA04.Range("AH5").Resize(Ary.Count).Value = Application.Transpose(Ary.toarray)
End Sub

 

[Fluff]

Ok, you obviously don't have the ArrayList, try

Sub KDS()
   Dim Dic As Object
   Dim Ary As Variant
   Dim Ws As Worksheet
    
   Set Dic = CreateObject("scripting.dictionary")
   With ThisWorkbook
      For Each Ws In .Worksheets
         Dic(Ws.CodeName) = Empty
      Next Ws
   End With
   Ary = SortAZ(Dic)
   GE03.Range("AH5").Resize(UBound(Ary) + 1).Value = Application.Transpose(Ary)
End Sub
Function SortAZ(InDic As Object) As Variant
    Dim Ary As Variant
    Dim i As Long, j As Long
    Dim Tmp As Variant
   
    Ary = InDic.keys
    For i = 0 To InDic.Count - 2
        For j = i + 1 To InDic.Count - 1
            If UCase(Ary(i)) > UCase(Ary(j)) Then
                Tmp = Ary(j)
                Ary(j) = Ary(i)
                Ary(i) = Tmp
            End If
        Next j
    Next i
    SortAZ = Ary
End Function

 

[KDS14589]

Ok, you obviously don't have the ArrayList, try

Sub KDS()
   Dim Dic As Object
   Dim Ary As Variant
   Dim Ws As Worksheet
    
   Set Dic = CreateObject("scripting.dictionary")
   With ThisWorkbook
      For Each Ws In .Worksheets
         Dic(Ws.CodeName) = Empty
      Next Ws
   End With
   Ary = SortAZ(Dic)
   Sheet4.Range("AH5").Resize(UBound(Ary)).Value = Application.Transpose(Ary)
End Sub
Function SortAZ(InDic As Object) As Variant
    Dim Ary As Variant
    Dim i As Long, j As Long
    Dim Tmp As Variant
   
    Ary = InDic.keys
    For i = 0 To InDic.Count - 2
        For j = i + 1 To InDic.Count - 1
            If UCase(Ary(i)) > UCase(Ary(j)) Then
                Tmp = Ary(j)
                Ary(j) = Ary(i)
                Ary(i) = Tmp
            End If
        Next j
    Next i
    SortAZ = Ary
End Function

IT WORKS!!!!!
Thanks
Sorry for all the trouble

 

[Fluff]

You're welcome & thanks for the feedback.

 

[KDS14589]

You're welcome & thanks for the feedback.

I was using the final solution until just a few minutes ago, it doesn't list the last worksheet. I tried adjusting your code but to no avail. I even went to the worksheet it listed on to see if I have blocked part of the list but 'no'.