short version of Index/Table of Contents
 

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.

short version of Index/Table of Contents
 

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.

short version of Index/Table of Contents
 

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.

short version of Index/Table of Contents
 

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.

short version of Index/Table of Contents
 

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.

short version of Index/Table of Contents
 

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

short version of Index/Table of Contents
 

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

short version of Index/Table of Contents
 

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

short version of Index/Table of Contents
 

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

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

short version of Index/Table of Contents
 

Noted with thanks, David !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"David McRitchie" wrote in message
...
You would want to change that Integer to Long
as it is referring to rows.

short version of Index/Table of Contents
 

True - but if the worksheets count was above 255 I would worry!

--
Cheers
Nigel



"David McRitchie" wrote in message
...
You would want to change that Integer to Long
as it is referring to rows.

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

short version of Index/Table of Contents
 

Point taken

"Nigel" wrote l...
True - but if the worksheets count was above 255 I would worry!

"David McRitchie" wrote ...
You would want to change that Integer to Long
as it is referring to rows.