SOLVED

Worksheet List

Copper Contributor

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 scroll through and look/move/copy the different sheets... but I would like to get that list onto something I can send.

 

Thanks in advance.

3 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@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

@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

thank you!
1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@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

View solution in original post