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

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

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

especially when you have a line like

Sheets.Add after:=Sheets(Sheets.Count): ActiveSheet.Name = "Sheet


--
Don Guillett
SalesAid Software

"Bob Phillips" wrote in message
...
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

Bob: Thanks for this IMPORTANT "added advice".
Thanks not only for helping in how to do things,
but also in jumping in to say how not to do things
-- it's the mark of a true teacher

"Bob Phillips" wrote in message
:

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

I like to keep a nice clean structure for loops and if structures but
for bread and butter bits of code I am happy to stack more than one
statement into a line
Guess it's just a bad habit, but everyone has their own style, and
whatever works for you is viable in my opinion.

Being that I am putting forward code for others to interpret then I
will try and clean up my act so to speak,

cheers

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

Jim May wrote:
Bob: Thanks for this IMPORTANT "added advice".
Thanks not only for helping in how to do things,
but also in jumping in to say how not to do things
-- it's the mark of a true teacher

"Bob Phillips" wrote in message
:

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

Most try to keep their lines short when posting in newsgroups - otherwise,
you oftern get wordwrap besides make the code look more complex - for new
users, they often don't recognize the wordwrap has occured and copy the
code as it literally appears. This can lead to errors or actually letting
the code run but with different functionality than intended.

--
Regards,
Tom Ogilvy



"somethinglikeant" wrote in message
ups.com...
I like to keep a nice clean structure for loops and if structures but
for bread and butter bits of code I am happy to stack more than one
statement into a line
Guess it's just a bad habit, but everyone has their own style, and
whatever works for you is viable in my opinion.

Being that I am putting forward code for others to interpret then I
will try and clean up my act so to speak,

cheers

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

Jim May wrote:
Bob: Thanks for this IMPORTANT "added advice".
Thanks not only for helping in how to do things,
but also in jumping in to say how not to do things
-- it's the mark of a true teacher

"Bob Phillips" wrote in message
:

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

Apologies for not following this fashion Tom,

It hadn't occurred to me that this had happened here.

Regards,

Anthony


Tom Ogilvy wrote:
Most try to keep their lines short when posting in newsgroups - otherwise,
you oftern get wordwrap besides make the code look more complex - for new
users, they often don't recognize the wordwrap has occured and copy the
code as it literally appears. This can lead to errors or actually letting
the code run but with different functionality than intended.

--
Regards,
Tom Ogilvy



"somethinglikeant" wrote in message
ups.com...
I like to keep a nice clean structure for loops and if structures but
for bread and butter bits of code I am happy to stack more than one
statement into a line
Guess it's just a bad habit, but everyone has their own style, and
whatever works for you is viable in my opinion.

Being that I am putting forward code for others to interpret then I
will try and clean up my act so to speak,

cheers

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

Jim May wrote:
Bob: Thanks for this IMPORTANT "added advice".
Thanks not only for helping in how to do things,
but also in jumping in to say how not to do things
-- it's the mark of a true teacher

"Bob Phillips" wrote in message
:

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