I need a macro to collect the names of every worksheet in a workbook such
that the names are in adjacent cells in one worksheet. The number of
worksheets can vary and ideally I don't want to collect the names of the last
2 sheets but I can always just delete them manually.

B3 = sheet1 name
B4 = sheet2 name
B5 = sheet 3 name and so on

Thx.

Run this sub in a new sheet (it'll list all sheetnames),
then just delete whatever is not required

Sub ListSheetNames()
Dim wkSht As Worksheet
Range("B2").Select
For Each wkSht In Worksheets
Selection = wkSht.Name
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
Next wkSht
End Sub

--
Max
Singapore
---
"navel151" wrote:
I need a macro to collect the names of every worksheet in a workbook such
that the names are in adjacent cells in one worksheet. The number of
worksheets can vary and ideally I don't want to collect the names of the last
2 sheets but I can always just delete them manually.

B3 = sheet1 name
B4 = sheet2 name
B5 = sheet 3 name and so on

TY :)

"Max" wrote:

Run this sub in a new sheet (it'll list all sheetnames),
then just delete whatever is not required

Sub ListSheetNames()
Dim wkSht As Worksheet
Range("B2").Select
For Each wkSht In Worksheets
Selection = wkSht.Name
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
Next wkSht
End Sub

--
Max
Singapore
---
"navel151" wrote:
I need a macro to collect the names of every worksheet in a workbook such
that the names are in adjacent cells in one worksheet. The number of
worksheets can vary and ideally I don't want to collect the names of the last
2 sheets but I can always just delete them manually.

B3 = sheet1 name
B4 = sheet2 name
B5 = sheet 3 name and so on

Sub CreateListOfSheetsOnFirstSheet()
Dim WS As Worksheet
For i = 1 To Worksheets.Count - 2
With Worksheets(1)
Set WS = Worksheets(i)
.Cells(i, 2).Value = WS.Name
End With
Next i
End Sub


Gord Dibben MS Excel MVP

On Sat, 20 Feb 2010 22:44:01 -0800, navel151
wrote:

I need a macro to collect the names of every worksheet in a workbook such
that the names are in adjacent cells in one worksheet. The number of
worksheets can vary and ideally I don't want to collect the names of the last
2 sheets but I can always just delete them manually.

B3 = sheet1 name
B4 = sheet2 name
B5 = sheet 3 name and so on

Thx.