List worksheet names

[Guest]

Hi,

I'm having trouble working out how to do this:

Loop through all worksheets in a workbook, returning the sheet names to a
range on the first sheet, i.e.
On sheet 1, cell A1 contains the name of the first worksheet, cell A2
contains the name of the second worksheet etc. Number of sheets is unknown.

Also to return the total number of sheets to cell B1.

Any thoughts?

Cheers,

Tom.

 

[Guest]

Try this to list names to the active sheet.

Sub listall()
x = 1
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
Cells(x, 1).Value = ws.Name
x = x + 1
Next ws
End Sub

Mike

 

[Guest]

Fantastic.

Just the job.

Tom.

Try this to list names to the active sheet.

Sub listall()
x = 1
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
Cells(x, 1).Value = ws.Name
x = x + 1
Next ws
End Sub

Mike

 

[Guest]

Sorry i missed the bit about the number of sheets. Add this line just before
the End Sub

Cells(1, 2).Value = x - 1