List of Worksheets

[doug.washburn]

Hello,

I have a workbook with dozens of worksheets inside it all with unique names.
I need to export a list of these worksheets to another document Like another Excel workbook).

I know how to get a list of the tabs from the properties>contents window, but I can't select them all to copy and paste.

Any ideas?

 

[VoG]

This will create a list of sheets in a new workbook:

Sub listsheets()
Dim i As Long, wb As Workbook, ws As Worksheet
Set wb = Workbooks.Add
For Each ws In ThisWorkbook.Worksheets
    i = i + 1
    wb.Sheets("Sheet1").Cells(i, 1).Value = ws.Name
Next ws
End Sub

 

[doug.washburn]

That works perfectly!

Awesome, thanks.

 

[hkruiniger]

Great idea, but I'm clearly not doing it right. I get a code snippet called something like FUNCRES.XSLAM, and when I run it I get "RES" in cell A1, nothing else....

???

Hans

 

[VoG]

Press ALT +F11, from trhe Insert menu select Module. Paste the code into the white space on the right. Press ALT + Q.

Press ALT F8, double click listsheets.

 

[hkruiniger]

Great, thanks for that!