List worksheet names
 

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.

List worksheet names
 

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

"mr tom" wrote:

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.

List worksheet names
 

Fantastic.

Just the job.

Tom.

"Mike" wrote:

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

"mr tom" wrote:

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.

List worksheet names
 

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

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

"Mike" wrote:

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

"mr tom" wrote:

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.