Forum Discussion
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 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.
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
3 Replies
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
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- thank you!
