List the names of sheets in an Excel document

I searched everywhere and I can not find a way to list all sheets of an Excel document. For example: Sheet1, sheet2 and finally Sheet 3. And I'd like to list them automatically in a worksheet:
A1 = Sheet1
A2 = Sheet2
A3 = Sheet3
etc...

It's just for a list of sheets in an excel workbook that has so.

You can definitely do that and it is very simple. Simply code something like as below:

Code:

---

For x = 1 To Excel.Workbooks(1).Worksheets.Count
  MsgBox (Excel.Worksheets(x).Name)
Next

---

Code:

---

Option Explicit
 
Dim Ar() As String
 
Sub Tst()
Dim Ws As Worksheet, i As Integer
Dim Ch As Chart
    With ShTst
        .Activate
        .Cells.Clear
    End With
    i = 0
    For Each Ws In ThisWorkbook.Worksheets
        i = i + 1
        With ShTst
            .Range("A" & i) = Ws.Name
            .Range("B" & i) = Ws.CodeName
        End With
    Next Ws
 
    For Each Ch In ThisWorkbook.Charts
        i = i + 1
        With ShTst
            .Range("A" & i) = Ch.Name
            .Range("B" & i) = Ch.CodeName
        End With
    Next Ch
End Sub
 
Sub Tst2()
    With ShTst
        .Activate
        .Cells.Clear
    End With
    ShTst.Range("A1:A" & UBound(Ar) + 1) = NameSheets
End Sub
 
Private Function NameSheets()
Dim i As Integer
    ReDim Ar(ThisWorkbook.Sheets.Count - 1)
    For i = 0 To ThisWorkbook.Sheets.Count - 1
        Ar(i) = Sheets(i + 1).Name
    Next i
    NameSheets = Application.WorksheetFunction.Transpose(Ar)
End Function

---

Here is a short macro to respond to your request, if ever you had not yet found, and possibly to serve others. You select from Sub to End Sub with the mouse, you do Ctrl + C and Ctrl + V in a module, and you throw.

Sub NAME_OF_SHEETS()
'Dim i As Integer, MyWorkbook As Workbook MySheet As Worksheet' OPTIONAL LINE

Sheets.Add before: = Sheets (1) 'adds a sheet in 1 position
'Set MySheet ActiveSheet =' OPTIONAL LINE

Set MyWorkbook = ActiveWorkbook
For i = 1 To MyClass.Worksheets.Count
Range ( "A" & i) = Sheets (i). Name
Range ( "B" & i) = i 'collar B indicates the position of the leaf' OPTIONAL LINE
Next
End Sub