Lesson#205: How to extract all sheet names from an Excel file

Step 1: Open the Excel file and access the VBA editor Open the Excel file that you want to extract the sheet names from. Press “Alt + F11” to open the VBA editor.

Step 2: Insert a new module In the VBA editor, click on “Insert” in the menu bar and select “Module.” This will insert a new module into your project.

Step 3: Write the VBA code In the module window, write the following VBA code:

Sub ExtractSheetNames() 
    Dim wb As Workbook 
    Dim ws As Worksheet 
    Dim sheetNames() As String 
    Dim targetCell As Range 
    Dim i As Integer 
     
    Set wb = ThisWorkbook 
    Set targetCell = Selection 
     
    ReDim sheetNames(1 To wb.Sheets.Count) 
     
    For Each ws In wb.Sheets 
        i = i + 1 
        sheetNames(i) = ws.Name 
    Next ws 
     
    targetCell.Resize(wb.Sheets.Count, 1).Value = Application.WorksheetFunction.Transpose(sheetNames) 
End Sub

Now run this code and get all sheet names extracted in one place.