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