Worksheet Name
 

on sheet1 in cell A1 I want for the name of sheet1 to
appear in this cell. In cell A2 I want for the name of
sheet2 to appear. In cell A3 I want for the name of
sheet3 to appear and so on until sheet50.....

This means A1:A50 will be a list of sheet names. Also
lets say if there are only 25 sheets instead of 50. If
this is the case I need for the code to leave the
remaining 25 cells in columnA blank and not put in a sheet
name because there are no sheets. So I need the code to
list the names of all the sheets but if there are no
sheets to leave the cell blank.

Thank you

Todd Huttenstine

Worksheet Name
 

Todd,

This routine

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Todd Huttenstine" wrote in message
...
on sheet1 in cell A1 I want for the name of sheet1 to
appear in this cell. In cell A2 I want for the name of
sheet2 to appear. In cell A3 I want for the name of
sheet3 to appear and so on until sheet50.....

This means A1:A50 will be a list of sheet names. Also
lets say if there are only 25 sheets instead of 50. If
this is the case I need for the code to leave the
remaining 25 cells in columnA blank and not put in a sheet
name because there are no sheets. So I need the code to
list the names of all the sheets but if there are no
sheets to leave the cell blank.

Thank you

Todd Huttenstine

Worksheet Name
 

Todd

Sub SheetNames()
Dim i As Long
For i = 1 To Worksheets.Count
Range("A" & i) = Sheets(i).Name
Next 'i
End Sub

Regards

Trevor


"Todd Huttenstine" wrote in message
...
on sheet1 in cell A1 I want for the name of sheet1 to
appear in this cell. In cell A2 I want for the name of
sheet2 to appear. In cell A3 I want for the name of
sheet3 to appear and so on until sheet50.....

This means A1:A50 will be a list of sheet names. Also
lets say if there are only 25 sheets instead of 50. If
this is the case I need for the code to leave the
remaining 25 cells in columnA blank and not put in a sheet
name because there are no sheets. So I need the code to
list the names of all the sheets but if there are no
sheets to leave the cell blank.

Thank you

Todd Huttenstine

Worksheet Name
 

Todd,

Sorry again, the touchpad is playing up tonight.

Here's the code.

Sub SheetNames()
Dim sh As Worksheet
Dim i As Long

Columns(1).ClearContents
i = 1
For Each sh In ThisWorkbook.Worksheets
ActiveSheet.Cells(i, "A").Value = sh.Name
i = i + 1
Next sh

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Todd Huttenstine" wrote in message
...
on sheet1 in cell A1 I want for the name of sheet1 to
appear in this cell. In cell A2 I want for the name of
sheet2 to appear. In cell A3 I want for the name of
sheet3 to appear and so on until sheet50.....

This means A1:A50 will be a list of sheet names. Also
lets say if there are only 25 sheets instead of 50. If
this is the case I need for the code to leave the
remaining 25 cells in columnA blank and not put in a sheet
name because there are no sheets. So I need the code to
list the names of all the sheets but if there are no
sheets to leave the cell blank.

Thank you

Todd Huttenstine

Worksheet Name
 

Thank you both


-----Original Message-----
on sheet1 in cell A1 I want for the name of sheet1 to
appear in this cell. In cell A2 I want for the name of
sheet2 to appear. In cell A3 I want for the name of
sheet3 to appear and so on until sheet50.....

This means A1:A50 will be a list of sheet names. Also
lets say if there are only 25 sheets instead of 50. If
this is the case I need for the code to leave the
remaining 25 cells in columnA blank and not put in a
sheet
name because there are no sheets. So I need the code to
list the names of all the sheets but if there are no
sheets to leave the cell blank.

Thank you

Todd Huttenstine
.

Worksheet Name
 

Sorry for this but I have to make a modification to this
code. Actually I would like for it to start listing the
names of the work sheets in Cell A3 instead of A1. And
also I need for it to start listing the names of the
worksheets on sheet3 instead of the first sheet in the
workbook.

Thank you.


-----Original Message-----
Todd,

Sorry again, the touchpad is playing up tonight.

Here's the code.

Sub SheetNames()
Dim sh As Worksheet
Dim i As Long

Columns(1).ClearContents
i = 1
For Each sh In ThisWorkbook.Worksheets
ActiveSheet.Cells(i, "A").Value = sh.Name
i = i + 1
Next sh

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Todd Huttenstine"
wrote in message
...
on sheet1 in cell A1 I want for the name of sheet1 to
appear in this cell. In cell A2 I want for the name of
sheet2 to appear. In cell A3 I want for the name of
sheet3 to appear and so on until sheet50.....

This means A1:A50 will be a list of sheet names. Also
lets say if there are only 25 sheets instead of 50. If
this is the case I need for the code to leave the
remaining 25 cells in columnA blank and not put in a
sheet
name because there are no sheets. So I need the code to
list the names of all the sheets but if there are no
sheets to leave the cell blank.

Thank you

Todd Huttenstine


.

Worksheet Name
 

Todd,

you are so demanding<vbg.

Hope this does it

Sub SheetNames()
Dim sh As Worksheet
Dim i As Long

Columns(1).ClearContents
i = 3
For Each sh In ThisWorkbook.Worksheets
Worksheets("Sheet3").Cells(i, "A").Value = sh.Name
i = i + 1
Next sh

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Todd Huttenstine" wrote in message
...
Sorry for this but I have to make a modification to this
code. Actually I would like for it to start listing the
names of the work sheets in Cell A3 instead of A1. And
also I need for it to start listing the names of the
worksheets on sheet3 instead of the first sheet in the
workbook.

Thank you.


-----Original Message-----
Todd,

Sorry again, the touchpad is playing up tonight.

Here's the code.

Sub SheetNames()
Dim sh As Worksheet
Dim i As Long

Columns(1).ClearContents
i = 1
For Each sh In ThisWorkbook.Worksheets
ActiveSheet.Cells(i, "A").Value = sh.Name
i = i + 1
Next sh

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Todd Huttenstine"
wrote in message
...
on sheet1 in cell A1 I want for the name of sheet1 to
appear in this cell. In cell A2 I want for the name of
sheet2 to appear. In cell A3 I want for the name of
sheet3 to appear and so on until sheet50.....

This means A1:A50 will be a list of sheet names. Also
lets say if there are only 25 sheets instead of 50. If
this is the case I need for the code to leave the
remaining 25 cells in columnA blank and not put in a
sheet
name because there are no sheets. So I need the code to
list the names of all the sheets but if there are no
sheets to leave the cell blank.

Thank you

Todd Huttenstine


.

Worksheet Name
 

I think Bob meant something like:

Sub SheetNames()
Dim sh As Worksheet
Dim i As Long

with Worksheets("Sheet3")
.Columns(1).ClearContents
i = 3
For Each sh In ThisWorkbook.Worksheets
.Cells(i, "A").Value = sh.Name
i = i + 1
Next sh
End with
End Sub

Otherwise it would clear columns(1) on the activesheet.

--
Regards,
Tom Ogilvy


Bob Phillips wrote in message
...
Todd,

you are so demanding<vbg.

Hope this does it

Sub SheetNames()
Dim sh As Worksheet
Dim i As Long

Columns(1).ClearContents
i = 3
For Each sh In ThisWorkbook.Worksheets
Worksheets("Sheet3").Cells(i, "A").Value = sh.Name
i = i + 1
Next sh

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Todd Huttenstine" wrote in message
...
Sorry for this but I have to make a modification to this
code. Actually I would like for it to start listing the
names of the work sheets in Cell A3 instead of A1. And
also I need for it to start listing the names of the
worksheets on sheet3 instead of the first sheet in the
workbook.

Thank you.


-----Original Message-----
Todd,

Sorry again, the touchpad is playing up tonight.

Here's the code.

Sub SheetNames()
Dim sh As Worksheet
Dim i As Long

Columns(1).ClearContents
i = 1
For Each sh In ThisWorkbook.Worksheets
ActiveSheet.Cells(i, "A").Value = sh.Name
i = i + 1
Next sh

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Todd Huttenstine"
wrote in message
...
on sheet1 in cell A1 I want for the name of sheet1 to
appear in this cell. In cell A2 I want for the name of
sheet2 to appear. In cell A3 I want for the name of
sheet3 to appear and so on until sheet50.....

This means A1:A50 will be a list of sheet names. Also
lets say if there are only 25 sheets instead of 50. If
this is the case I need for the code to leave the
remaining 25 cells in columnA blank and not put in a
sheet
name because there are no sheets. So I need the code to
list the names of all the sheets but if there are no
sheets to leave the cell blank.

Thank you

Todd Huttenstine


.

Worksheet Name
 

Its almost done. Currently, the code lists the names of
all worksheets, but instead, I need it to start listing
the names of all worksheets starting with the name of
sheet3.

Thanx



-----Original Message-----
Todd,

you are so demanding<vbg.

Hope this does it

Sub SheetNames()
Dim sh As Worksheet
Dim i As Long

Columns(1).ClearContents
i = 3
For Each sh In ThisWorkbook.Worksheets
Worksheets("Sheet3").Cells(i, "A").Value = sh.Name
i = i + 1
Next sh

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Todd Huttenstine"
wrote in message
...
Sorry for this but I have to make a modification to this
code. Actually I would like for it to start listing the
names of the work sheets in Cell A3 instead of A1. And
also I need for it to start listing the names of the
worksheets on sheet3 instead of the first sheet in the
workbook.

Thank you.


-----Original Message-----
Todd,

Sorry again, the touchpad is playing up tonight.

Here's the code.

Sub SheetNames()
Dim sh As Worksheet
Dim i As Long

Columns(1).ClearContents
i = 1
For Each sh In ThisWorkbook.Worksheets
ActiveSheet.Cells(i, "A").Value = sh.Name
i = i + 1
Next sh

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the
Purbecks
(remove nothere from the email address if mailing
direct)

"Todd Huttenstine"

wrote in message
...
on sheet1 in cell A1 I want for the name of sheet1 to
appear in this cell. In cell A2 I want for the name
of
sheet2 to appear. In cell A3 I want for the name of
sheet3 to appear and so on until sheet50.....

This means A1:A50 will be a list of sheet names.
Also
lets say if there are only 25 sheets instead of 50.
If
this is the case I need for the code to leave the
remaining 25 cells in columnA blank and not put in a
sheet
name because there are no sheets. So I need the
code to
list the names of all the sheets but if there are no
sheets to leave the cell blank.

Thank you

Todd Huttenstine


.



.

Worksheet Name
 

Sub SheetNames()
Dim sh As Worksheet
Dim i As Long
Dim bFlag as Boolean

with Worksheets("Sheet3")
.Columns(1).ClearContents
i = 3
For Each sh In ThisWorkbook.Worksheets
if lcase(sh.name) = "sheet3" then bFlag = True
if bFlag then
.Cells(i, "A").Value = sh.Name
i = i + 1
end if
Next sh
End with
End Sub

--
Regards,
Tom Ogilvy


Tom Ogilvy wrote in message
...
I think Bob meant something like:

Sub SheetNames()
Dim sh As Worksheet
Dim i As Long

with Worksheets("Sheet3")
.Columns(1).ClearContents
i = 3
For Each sh In ThisWorkbook.Worksheets
.Cells(i, "A").Value = sh.Name
i = i + 1
Next sh
End with
End Sub

Otherwise it would clear columns(1) on the activesheet.

--
Regards,
Tom Ogilvy


Bob Phillips wrote in message
...
Todd,

you are so demanding<vbg.

Hope this does it

Sub SheetNames()
Dim sh As Worksheet
Dim i As Long

Columns(1).ClearContents
i = 3
For Each sh In ThisWorkbook.Worksheets
Worksheets("Sheet3").Cells(i, "A").Value = sh.Name
i = i + 1
Next sh

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Todd Huttenstine" wrote in
message
...
Sorry for this but I have to make a modification to this
code. Actually I would like for it to start listing the
names of the work sheets in Cell A3 instead of A1. And
also I need for it to start listing the names of the
worksheets on sheet3 instead of the first sheet in the
workbook.

Thank you.


-----Original Message-----
Todd,

Sorry again, the touchpad is playing up tonight.

Here's the code.

Sub SheetNames()
Dim sh As Worksheet
Dim i As Long

Columns(1).ClearContents
i = 1
For Each sh In ThisWorkbook.Worksheets
ActiveSheet.Cells(i, "A").Value = sh.Name
i = i + 1
Next sh

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Todd Huttenstine"
wrote in message
...
on sheet1 in cell A1 I want for the name of sheet1 to
appear in this cell. In cell A2 I want for the name of
sheet2 to appear. In cell A3 I want for the name of
sheet3 to appear and so on until sheet50.....

This means A1:A50 will be a list of sheet names. Also
lets say if there are only 25 sheets instead of 50. If
this is the case I need for the code to leave the
remaining 25 cells in columnA blank and not put in a
sheet
name because there are no sheets. So I need the code to
list the names of all the sheets but if there are no
sheets to leave the cell blank.

Thank you

Todd Huttenstine


.

Worksheet Name
 

I have modified the code (see below)
Here is the problem. According to the current code, the
first sheet displayed is "template"(because it is written
in the code), however I need it to start with the next
sheet which is unknown because the name of that sheet may
change. That sheet will always be sheet number 4 so is
there anyway to specify to start with sheet number 4? For
instance. The next sheet after sheet "template" is
called "Tammy". The code needs to specify to start
listing the sheets with sheet4 (which in this case
is "Tammy") Therefor I need to specify in the code to
start listing sheets with sheet4, instead of the name of
the actual sheet.



Dim sh As Worksheet
Dim i As Long
Dim bFlag As Boolean

With Worksheets(2)
.Columns(10).ClearContents
i = 3
For Each sh In ThisWorkbook.Worksheets
If LCase(sh.name) = "template" Then bFlag = True
If bFlag Then
.Cells(i, "J").Value = sh.name
i = i + 1
End If
Next sh
End With

Worksheet Name
 

Sub SheetNames()
Dim sh As Worksheet
Dim i As Long

with Worksheets("Sheet3")
.Columns(1).ClearContents
j = 0
i = 3
For Each sh In ThisWorkbook.Worksheets
j = j + 1
if j = 4 then
.Cells(i, "A").Value = sh.Name
i = i + 1
end if
Next sh
End with
End Sub

or

Sub SheetNames()
Dim sh As Worksheet
Dim i As Long
Dim bFlag as Boolean

with Worksheets("Sheet3")
.Columns(1).ClearContents
i = 3
For Each sh In ThisWorkbook.Worksheets
if bFlag then
.Cells(i, "A").Value = sh.Name
i = i + 1
end if
if lcase(sh.name) = "template" then bFlag = True
Next sh
End with
End Sub

--
Regards,
Tom Ogilvy





Todd Huttenstine wrote in message
...
I have modified the code (see below)
Here is the problem. According to the current code, the
first sheet displayed is "template"(because it is written
in the code), however I need it to start with the next
sheet which is unknown because the name of that sheet may
change. That sheet will always be sheet number 4 so is
there anyway to specify to start with sheet number 4? For
instance. The next sheet after sheet "template" is
called "Tammy". The code needs to specify to start
listing the sheets with sheet4 (which in this case
is "Tammy") Therefor I need to specify in the code to
start listing sheets with sheet4, instead of the name of
the actual sheet.



Dim sh As Worksheet
Dim i As Long
Dim bFlag As Boolean

With Worksheets(2)
.Columns(10).ClearContents
i = 3
For Each sh In ThisWorkbook.Worksheets
If LCase(sh.name) = "template" Then bFlag = True
If bFlag Then
.Cells(i, "J").Value = sh.name
i = i + 1
End If
Next sh
End With

Worksheet Name
 

Missed your code

With Worksheets(2)
.Columns(10).ClearContents
i = 3
For Each sh In ThisWorkbook.Worksheets
If bFlag Then
.Cells(i, "J").Value = sh.name
i = i + 1
End If
If LCase(sh.name) = "template" Then bFlag = True
Next sh
End With

To start adding names after sheet template in the tab order

--
Regards,
Tom Ogilvy




Todd Huttenstine wrote in message
...
I have modified the code (see below)
Here is the problem. According to the current code, the
first sheet displayed is "template"(because it is written
in the code), however I need it to start with the next
sheet which is unknown because the name of that sheet may
change. That sheet will always be sheet number 4 so is
there anyway to specify to start with sheet number 4? For
instance. The next sheet after sheet "template" is
called "Tammy". The code needs to specify to start
listing the sheets with sheet4 (which in this case
is "Tammy") Therefor I need to specify in the code to
start listing sheets with sheet4, instead of the name of
the actual sheet.



Dim sh As Worksheet
Dim i As Long
Dim bFlag As Boolean

With Worksheets(2)
.Columns(10).ClearContents
i = 3
For Each sh In ThisWorkbook.Worksheets
If LCase(sh.name) = "template" Then bFlag = True
If bFlag Then
.Cells(i, "J").Value = sh.name
i = i + 1
End If
Next sh
End With

Worksheet Name
 

That got it! Thanks again.


-----Original Message-----
Sub SheetNames()
Dim sh As Worksheet
Dim i As Long

with Worksheets("Sheet3")
.Columns(1).ClearContents
j = 0
i = 3
For Each sh In ThisWorkbook.Worksheets
j = j + 1
if j = 4 then
.Cells(i, "A").Value = sh.Name
i = i + 1
end if
Next sh
End with
End Sub

or

Sub SheetNames()
Dim sh As Worksheet
Dim i As Long
Dim bFlag as Boolean

with Worksheets("Sheet3")
.Columns(1).ClearContents
i = 3
For Each sh In ThisWorkbook.Worksheets
if bFlag then
.Cells(i, "A").Value = sh.Name
i = i + 1
end if
if lcase(sh.name) = "template" then bFlag = True
Next sh
End with
End Sub

--
Regards,
Tom Ogilvy





Todd Huttenstine
wrote in message
...
I have modified the code (see below)
Here is the problem. According to the current code, the
first sheet displayed is "template"(because it is
written
in the code), however I need it to start with the next
sheet which is unknown because the name of that sheet
may
change. That sheet will always be sheet number 4 so is
there anyway to specify to start with sheet number 4?
For
instance. The next sheet after sheet "template" is
called "Tammy". The code needs to specify to start
listing the sheets with sheet4 (which in this case
is "Tammy") Therefor I need to specify in the code to
start listing sheets with sheet4, instead of the name of
the actual sheet.



Dim sh As Worksheet
Dim i As Long
Dim bFlag As Boolean

With Worksheets(2)
.Columns(10).ClearContents
i = 3
For Each sh In ThisWorkbook.Worksheets
If LCase(sh.name) = "template" Then bFlag = True
If bFlag Then
.Cells(i, "J").Value = sh.name
i = i + 1
End If
Next sh
End With


.