Yesterday I saw a short formula that made a simple TOC of worksheet (tab)
names. By entering a new sheet and using code, I was able to return in that
new sheet the list of all worksheet names for the user to see quickly. The
code did not save and I cannot find it.

It was not the long version posted in most responses via the hyperlink to
mvps.org.

If anyone gave that other short answer, please respond. thanks.

Sub MakeTOC()
Dim sh as Worksheet
Dim i as Long
i = 1
Worksheets(1).Cells(1,1).Value = "Sheets"
for each sh in thisworkbook.Worksheets
i = i + 1
worksheets(1).Cells(i,1).Value = sh.name
next
End Sub

--
Regards,
Tom Ogilvy


"amyc" wrote in message
...
Yesterday I saw a short formula that made a simple TOC of worksheet (tab)
names. By entering a new sheet and using code, I was able to return in
that
new sheet the list of all worksheet names for the user to see quickly.
The
code did not save and I cannot find it.

It was not the long version posted in most responses via the hyperlink to
mvps.org.

If anyone gave that other short answer, please respond. thanks.

thank you - that's it!

"Tom Ogilvy" wrote:

Sub MakeTOC()
Dim sh as Worksheet
Dim i as Long
i = 1
Worksheets(1).Cells(1,1).Value = "Sheets"
for each sh in thisworkbook.Worksheets
i = i + 1
worksheets(1).Cells(i,1).Value = sh.name
next
End Sub

--
Regards,
Tom Ogilvy


"amyc" wrote in message
...
Yesterday I saw a short formula that made a simple TOC of worksheet (tab)
names. By entering a new sheet and using code, I was able to return in
that
new sheet the list of all worksheet names for the user to see quickly.
The
code did not save and I cannot find it.

It was not the long version posted in most responses via the hyperlink to
mvps.org.

If anyone gave that other short answer, please respond. thanks.

Not sure what you saw but this is a simple way of listing all worksheets in
workbook on the active worksheet - it might be what you need?

Sub Toc()
Dim xrow As Integer
For xrow = 1 To wb.Worksheets.Count
Cells(xrow, 1) = Worksheets(xrow).Name
Next
End Sub

--
Cheers
Nigel



"amyc" wrote in message
...
Yesterday I saw a short formula that made a simple TOC of worksheet (tab)
names. By entering a new sheet and using code, I was able to return in
that
new sheet the list of all worksheet names for the user to see quickly.
The
code did not save and I cannot find it.

It was not the long version posted in most responses via the hyperlink to
mvps.org.

If anyone gave that other short answer, please respond. thanks.

Hi, despite several tries at running your sub,
it froze at this line:

For xrow = 1 To wb.Worksheets.Count

What can I do ?
Thanks
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Nigel" wrote in message
...
Not sure what you saw but this is a simple way of listing all worksheets
in
workbook on the active worksheet - it might be what you need?

Sub Toc()
Dim xrow As Integer
For xrow = 1 To wb.Worksheets.Count
Cells(xrow, 1) = Worksheets(xrow).Name
Next
End Sub

Apologies - modify as follows.......

Dim wb As Workbook
Set wb = ActiveWorkbook
Dim xrow As Integer
For xrow = 1 To wb.Worksheets.Count
Cells(xrow, 1) = Worksheets(xrow).Name
Next

OR use this ........

Dim xrow As Integer
For xrow = 1 To ActiveWorkbook.Worksheets.Count
Cells(xrow, 1) = Worksheets(xrow).Name
Next



--
Cheers
Nigel



"Max" wrote in message
...
Hi, despite several tries at running your sub,
it froze at this line:

For xrow = 1 To wb.Worksheets.Count

What can I do ?
Thanks
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Nigel" wrote in message
...
Not sure what you saw but this is a simple way of listing all worksheets
in
workbook on the active worksheet - it might be what you need?

Sub Toc()
Dim xrow As Integer
For xrow = 1 To wb.Worksheets.Count
Cells(xrow, 1) = Worksheets(xrow).Name
Next
End Sub

Thanks, Nigel !
Runs great now ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

You would want to change that Integer to Long
as it is referring to rows.

"Max" wrote in message ...
Thanks, Nigel !
Runs great now ..

I would suggest that you sort your list

Simple macro to populate with Sheetnames with a Sort (#SheetNamesSortedDownRows)
://www.mvps.org/dmcritchie/excel/buildtoc2.htm#SheetNamesSortedDownRows

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"amyc" wrote in message ...
Yesterday I saw a short formula that made a simple TOC of worksheet (tab)
names. By entering a new sheet and using code, I was able to return in that
new sheet the list of all worksheet names for the user to see quickly. The
code did not save and I cannot find it.

It was not the long version posted in most responses via the hyperlink to
mvps.org.

If anyone gave that other short answer, please respond. thanks.