List of Sheets

[Jahsonn]

Hi Guys....

Is there an easy way to list all sheet names from a
workbook in a sheet on the workbook?

TIA

 

[Frank Kabel]

Hi
have a look at
http://www.mvps.org/dmcritchie/excel/buildtoc.htm

 

[Bob Phillips]

i=1
For Each sh In Activeworkbook.Sheets
Cells(i,"A").Value = sh.Name
i=i+1
Next sh

--

HTH

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

 

[Tom Ogilvy]

Dim rw as Long, sh as Worksheet
rw = 3
for each sh in Activeworkbook.worksheets
worksheets("Listing").Cells(rw,1).Value = sh.name
rw = rw + 1
Next

 

[Jahsonn]

Hi Thanks For your posts,

I have tried amending the code so that it will only
display names of sheets after the 4th sheet.

Sub test()

With Worksheets("Control")
i = 17
For sh = 5 To Worksheets.Count
Cells(i, "B").Value = sh.Name
i = i + 1
Next sh
End With

End Sub

But this is coming up with an error - Can anyone help?

 

[Tom Ogilvy]

Dim rw as Long, sh as Worksheet
Dim bStart as Boolean
rw = 17
for each sh in Activeworkbook.worksheets
if bStart then
worksheets("Control").Cells(rw,2).Value = sh.name
rw = rw + 1
End if
if sh.Name = worksheets(4).Name then bStart = true
Next

 

[Bob Phillips]

In this instance, sh is an object not an index. Try

Sub test()

With Worksheets("Control")
i = 17
For sh = 5 To Worksheets.Count
Cells(i, "B").Value = worksheets(sh).Name
Next sh
End With

End Sub


--

HTH

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

 

[Dnk]

Try this:

Sub WorkSheetNames()
Dim sht As Worksheet
Dim mySht As Worksheet
Set mySht = Sheets("Sheet1")
Dim r As Integer
Dim c As Integer
r = 1
c = 1
For Each sht In Worksheets
mySht.Cells(r, c) = sht.Name
r = r + 1
Next sht
End Sub