VBA : List Sheet Names in Excel

[kimpro82]

By ChatGPT

• Get the list

  Sub ListSheetNames()
    Dim ws As Worksheet
  
    ' Loop through each worksheet in the active workbook
    For Each ws In ThisWorkbook.Sheets
        ' Print the name of each worksheet
        Debug.Print ws.Name
    Next ws
  End Sub

[kimpro82]

By ChatGPT

• Print on the sheet

  Sub ListSheetNamesToArrayAndPrint()
    Dim ws As Worksheet
    Dim sheetNames() As String
    Dim i As Integer
  
    ' Resize the array to the number of sheets in the workbook
    ReDim sheetNames(1 To Sheets.Count)
  
    ' Loop through each worksheet and store its name in the array
    For Each ws In ThisWorkbook.Sheets
        i = i + 1
        sheetNames(i) = ws.Name
    Next ws
  
    ' Print sheet names on the active sheet
    For i = LBound(sheetNames) To UBound(sheetNames)
        Cells(i + 1, 1).Value = sheetNames(i)
    Next i
  End Sub

[kimpro82]

By ChatGPT

• Print without For loop

    ……
  
    ' Print sheet names in a row on the active sheet
    Range("A1").Resize(, UBound(sheetNames)) = sheetNames
    ……