Worksheet name list from range....
 

I need a code that will place the list of worksheet names in a
workbook.
However I have two exceptions....

(1) I need the names to be listed starting at a specific cell. (a46)
(2) I need to dictate the range of sheet names reported.
(start name list based on 5th sheet in workbook ending at "sheet y")


Thanx.

Worksheet name list from range....
 

Dim i as Long, j as Long, sh as Worksheet
Dim first as Long, last as Long

first = 5
last = 10
i = 0
j = 45
for each sh in Workbook.Worksheets
i = i + 1
if i = first and i <= last then
j = j + 1
Cells(j,"A").Value = sh.name
end if
Next

--
Regards,
Tom Ogilvy


"J.W. Aldridge" wrote:

I need a code that will place the list of worksheet names in a
workbook.
However I have two exceptions....

(1) I need the names to be listed starting at a specific cell. (a46)
(2) I need to dictate the range of sheet names reported.
(start name list based on 5th sheet in workbook ending at "sheet y")


Thanx.

Worksheet name list from range....
 

Dim iStart as long
Dim iEnd As long
Dim i As Long
Dim StartCell as Range

iStart = 5 '<===== change to suit
iEnd = 9 '<===== change to suit
Set StarCell = Range("A46") '<===== change to suit

For i = iStart to iEnd
StarCell.Offset(i-1,0).Value = Worksheets(i).Name
Next i


--
HTH

Bob Phillips

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

"J.W. Aldridge" wrote in message
ps.com...
I need a code that will place the list of worksheet names in a
workbook.
However I have two exceptions....

(1) I need the names to be listed starting at a specific cell. (a46)
(2) I need to dictate the range of sheet names reported.
(start name list based on 5th sheet in workbook ending at "sheet y")


Thanx.

Worksheet name list from range....
 

thanx.
Both codes work well however, I only have one problem....

The first sheet will always be entitled "Start" and will be the 5th
sheet.
However, the last sheet number may vary. So, I need it to stop at sheet
"end".

Worksheet name list from range....
 

Dim iStart as long
Dim iEnd As long
Dim i As Long
Dim StartCell as Range

iStart = Worksheets("start").Index
iEnd = Worksheets("end").Index
Set StarCell = Range("A46") '<===== change to suit

For i = iStart to iEnd
StarCell.Offset(i-1,0).Value = Worksheets(i).Name
Next i


--
HTH

Bob Phillips

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

"J.W. Aldridge" wrote in message
ups.com...
thanx.
Both codes work well however, I only have one problem....

The first sheet will always be entitled "Start" and will be the 5th
sheet.
However, the last sheet number may vary. So, I need it to stop at sheet
"end".

Worksheet name list from range....
 

Note that worksheets("Start").Index returns an index from the sheets
collection (not the worksheets collection). Not a problem if you only have
worksheets. But to be robust you might use Sheets throughout Bob's code
rather than Worksheets.

just to illustrate with the tab order showing

Sheet1
Chart1
Sheet2
Sheet3

from the immediate window:

? worksheets("sheet2").Index
3
? worksheets(3).Name
Sheet3

--
Regards,
Tom Ogilvy



"Bob Phillips" wrote:

Dim iStart as long
Dim iEnd As long
Dim i As Long
Dim StartCell as Range

iStart = Worksheets("start").Index
iEnd = Worksheets("end").Index
Set StarCell = Range("A46") '<===== change to suit

For i = iStart to iEnd
StarCell.Offset(i-1,0).Value = Worksheets(i).Name
Next i


--
HTH

Bob Phillips

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

"J.W. Aldridge" wrote in message
ups.com...
thanx.
Both codes work well however, I only have one problem....

The first sheet will always be entitled "Start" and will be the 5th
sheet.
However, the last sheet number may vary. So, I need it to stop at sheet
"end".

Worksheet name list from range....
 

Dim j as Long, sh as Worksheet
Dim first as string, last as string
Dim bStart as Boolean

first = "start"
last = "end"
j = 45
bStart = False
for each sh in Workbook.Worksheets
if lcase(sh.name) = first then bStart = True
if bStart then
j = j + 1
Cells(j,"A").Value = sh.name
end if
if lcase(sh.Name) = "end" then exit for
Next

--
Regards,
Tom Ogilvy

"J.W. Aldridge" wrote:

thanx.
Both codes work well however, I only have one problem....

The first sheet will always be entitled "Start" and will be the 5th
sheet.
However, the last sheet number may vary. So, I need it to stop at sheet
"end".

Worksheet name list from range....
 

Thanx All,


Code worked puifectly!