Hi there,

Can anybody help me with the code the generate a list of all the sheet names
in my workbook.

Preferably the code should create a new sheet in the workbook and then list
all the names starting in cell A1, going down.

Thanks for the help.

Regards,

Steven P

Hi Steven this should do it

Sub ListSheets()
Sheets.Add after:=Sheets(Sheets.Count): ActiveSheet.Name = "Sheet
Names"
[A1] = "Sheet Names": ActiveCell.Font.Bold = True: ActiveCell.Offset(1,
0).Select
Dim sht As Worksheet
For Each sht In Worksheets
If sht.Name < "Sheet Names" Then
ActiveCell = sht.Name
ActiveCell.Offset(1, 0).Select
End If
Next sht
End Sub

http://www.excel-ant.co.uk




StevenP wrote:
Hi there,

Can anybody help me with the code the generate a list of all the sheet names
in my workbook.

Preferably the code should create a new sheet in the workbook and then list
all the names starting in cell A1, going down.

Thanks for the help.

Regards,

Steven P

Works fantastic, thanks very much !!!!

Will have a look at your website, for some more hints and tips.

Regards,
Steven

"somethinglikeant" wrote:

Hi Steven this should do it

Sub ListSheets()
Sheets.Add after:=Sheets(Sheets.Count): ActiveSheet.Name = "Sheet
Names"
[A1] = "Sheet Names": ActiveCell.Font.Bold = True: ActiveCell.Offset(1,
0).Select
Dim sht As Worksheet
For Each sht In Worksheets
If sht.Name < "Sheet Names" Then
ActiveCell = sht.Name
ActiveCell.Offset(1, 0).Select
End If
Next sht
End Sub

http://www.excel-ant.co.uk




StevenP wrote:
Hi there,

Can anybody help me with the code the generate a list of all the sheet names
in my workbook.

Preferably the code should create a new sheet in the workbook and then list
all the names starting in cell A1, going down.

Thanks for the help.

Regards,

Steven P

This is great, it works fantastically well. Thanks.

I will have a look at your website for some more hints and tips.

Regards,
Steven

"somethinglikeant" wrote:

Hi Steven this should do it

Sub ListSheets()
Sheets.Add after:=Sheets(Sheets.Count): ActiveSheet.Name = "Sheet
Names"
[A1] = "Sheet Names": ActiveCell.Font.Bold = True: ActiveCell.Offset(1,
0).Select
Dim sht As Worksheet
For Each sht In Worksheets
If sht.Name < "Sheet Names" Then
ActiveCell = sht.Name
ActiveCell.Offset(1, 0).Select
End If
Next sht
End Sub

http://www.excel-ant.co.uk




StevenP wrote:
Hi there,

Can anybody help me with the code the generate a list of all the sheet names
in my workbook.

Preferably the code should create a new sheet in the workbook and then list
all the names starting in cell A1, going down.

Thanks for the help.

Regards,

Steven P

Hi somethinglikeant;
Stepped through your code mostly to see the "affect" of
The two ":"'s in line 2 and 3. Noted that they serve
To act as Separators or parameters; just hadn't noticed
This before being faily new to all this. Can you comment
On their use/purpose, or have I guessed correctly?
TIA,


"somethinglikeant" wrote in message
oups.com:

Hi Steven this should do it

Sub ListSheets()
Sheets.Add after:=Sheets(Sheets.Count): ActiveSheet.Name = "Sheet
Names"
[A1] = "Sheet Names": ActiveCell.Font.Bold = True: ActiveCell.Offset(1,
0).Select
Dim sht As Worksheet
For Each sht In Worksheets
If sht.Name < "Sheet Names" Then
ActiveCell = sht.Name
ActiveCell.Offset(1, 0).Select
End If
Next sht
End Sub

http://www.excel-ant.co.uk




StevenP wrote:

Hi there,

Can anybody help me with the code the generate a list of all the sheet names
in my workbook.

Preferably the code should create a new sheet in the workbook and then list
all the names starting in cell A1, going down.

Thanks for the help.

Regards,

Steven P

just lets you put several lines on one line
a
b
c
a:b:c

--
Don Guillett
SalesAid Software

"Jim May" wrote in message
news:0n2zg.105277$IZ2.198@dukeread07...
Hi somethinglikeant;
Stepped through your code mostly to see the "affect" of
The two ":"'s in line 2 and 3. Noted that they serve
To act as Separators or parameters; just hadn't noticed
This before being faily new to all this. Can you comment
On their use/purpose, or have I guessed correctly?
TIA,


"somethinglikeant" wrote in message
oups.com:

Hi Steven this should do it

Sub ListSheets()
Sheets.Add after:=Sheets(Sheets.Count): ActiveSheet.Name = "Sheet
Names"
[A1] = "Sheet Names": ActiveCell.Font.Bold = True: ActiveCell.Offset(1,
0).Select
Dim sht As Worksheet
For Each sht In Worksheets
If sht.Name < "Sheet Names" Then
ActiveCell = sht.Name
ActiveCell.Offset(1, 0).Select
End If
Next sht
End Sub

http://www.excel-ant.co.uk




StevenP wrote:

Hi there,

Can anybody help me with the code the generate a list of all the sheet
names
in my workbook.

Preferably the code should create a new sheet in the workbook and then
list
all the names starting in cell A1, going down.

Thanks for the help.

Regards,

Steven P

Thanks Don;

"Don Guillett" wrote in message
:

just lets you put several lines on one line
a
b
c
a:b:c

--
Don Guillett
SalesAid Software

"Jim May" wrote in message
news:0n2zg.105277$IZ2.198@dukeread07...

Hi somethinglikeant;
Stepped through your code mostly to see the "affect" of
The two ":"'s in line 2 and 3. Noted that they serve
To act as Separators or parameters; just hadn't noticed
This before being faily new to all this. Can you comment
On their use/purpose, or have I guessed correctly?
TIA,


"somethinglikeant" wrote in message
oups.com:


Hi Steven this should do it

Sub ListSheets()
Sheets.Add after:=Sheets(Sheets.Count): ActiveSheet.Name = "Sheet
Names"
[A1] = "Sheet Names": ActiveCell.Font.Bold = True: ActiveCell.Offset(1,
0).Select
Dim sht As Worksheet
For Each sht In Worksheets
If sht.Name < "Sheet Names" Then
ActiveCell = sht.Name
ActiveCell.Offset(1, 0).Select
End If
Next sht
End Sub

http://www.excel-ant.co.uk




StevenP wrote:


Hi there,

Can anybody help me with the code the generate a list of all the sheet
names
in my workbook.

Preferably the code should create a new sheet in the workbook and then
list
all the names starting in cell A1, going down.

Thanks for the help.

Regards,

Steven P

Don't practice it Jim, it makes the code harder to read IMO.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jim May" wrote in message
news:1b3zg.105279$IZ2.40215@dukeread07...
Thanks Don;

"Don Guillett" wrote in message
:

just lets you put several lines on one line
a
b
c
a:b:c

--
Don Guillett
SalesAid Software

"Jim May" wrote in message
news:0n2zg.105277$IZ2.198@dukeread07...

Hi somethinglikeant;
Stepped through your code mostly to see the "affect" of
The two ":"'s in line 2 and 3. Noted that they serve
To act as Separators or parameters; just hadn't noticed
This before being faily new to all this. Can you comment
On their use/purpose, or have I guessed correctly?
TIA,


"somethinglikeant" wrote in message
oups.com:


Hi Steven this should do it

Sub ListSheets()
Sheets.Add after:=Sheets(Sheets.Count): ActiveSheet.Name = "Sheet
Names"
[A1] = "Sheet Names": ActiveCell.Font.Bold = True:
ActiveCell.Offset(1,
0).Select
Dim sht As Worksheet
For Each sht In Worksheets
If sht.Name < "Sheet Names" Then
ActiveCell = sht.Name
ActiveCell.Offset(1, 0).Select
End If
Next sht
End Sub

http://www.excel-ant.co.uk




StevenP wrote:


Hi there,

Can anybody help me with the code the generate a list of all the
sheet
names
in my workbook.

Preferably the code should create a new sheet in the workbook and
then
list
all the names starting in cell A1, going down.

Thanks for the help.

Regards,

Steven P

You may find this to be more efficient for your list

Sub listsheets()
For i = 1 To Worksheets.Count
Cells(i, "a") = Sheets(i).Name
Next i
End Sub



--
Don Guillett
SalesAid Software

"StevenP" wrote in message
...
Hi there,

Can anybody help me with the code the generate a list of all the sheet
names
in my workbook.

Preferably the code should create a new sheet in the workbook and then
list
all the names starting in cell A1, going down.

Thanks for the help.

Regards,

Steven P