Forum Discussion

Copper Contributor

May 03, 2021

Solved

Worksheet List

I need to send a list of the worksheet names... I have hundreds. Is there a way that I can generate a list of all the worksheet names to send to someone? I know you can right click on the bottom to s...

excel

HansVogelaar

May 03, 2021

kelseylacroix

The following macro will create a list of all workheets in the active workbook. The list will be placed in a new workbook.

You can send that workbook, or copy the list and paste it into an email message - whichever you prefer.

Sub ListSheets()
    Dim wbkS As Workbook
    Dim wshS As Worksheet
    Dim wbkT As Workbook
    Dim wshT As Worksheet
    Dim r As Long
    Application.ScreenUpdating = False
    Set wbkS = ActiveWorkbook
    Set wbkT = Workbooks.Add(xlWBATWorksheet)
    Set wshT = wbkT.Worksheets(1)
    For Each wshS In wbkS.Worksheets
        r = r + 1
        wshT.Range("A" & r).Value = wshS.Name
    Next wshS
    Application.ScreenUpdating = True
End Sub

Riny_van_Eekelen

Platinum Contributor

May 03, 2021

kelseylacroix Or, if you are into Power Query, you could connect to a saved version of the workbook itself and apply some basic steps. The script in the Advanced Editor could look like this:

let
    Source = Excel.Workbook(File.Contents("<path and file name>.xlsx"), null, true),
    #"Filtered Rows" = Table.SelectRows(Source, each [Kind] = "Sheet"),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name"})
in
    #"Removed Other Columns"

where you would enter the full path and file name between the quotes on the first line after "let".

Close and load to an Excel table