May 03 2021 08:16 AM
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.
May 03 2021 08:35 AM
SolutionThe 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
May 03 2021 08:44 AM
@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
May 03 2021 08:35 AM
SolutionThe 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