List the names of sheets in an Excel document

[ASHER]

Sponsored Links

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.

[kelfro]

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

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

[fellah]

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

[Reegan]

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