I have 15 or more worksheet tabs. How do I make a list/auto-populate of all
the name of the tabs on one worksheet to form the basis of an index?

sub toc()
for i=1 to sheets.count
cells(i,1).value=sheets(i).name
next i
end sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"S G Tan" wrote in message
...
I have 15 or more worksheet tabs. How do I make a list/auto-populate of all
the name of the tabs on one worksheet to form the basis of an index?

When I do that, it defaults to the top of the page and start the list. Is it
possible to change the macro,so that it list the start of the TOC at the cell
that I specify?

"Don Guillett" wrote:

sub toc()
for i=1 to sheets.count
cells(i,1).value=sheets(i).name
next i
end sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"S G Tan" wrote in message
...
I have 15 or more worksheet tabs. How do I make a list/auto-populate of all
the name of the tabs on one worksheet to form the basis of an index?

Sub toc()
Dim i, c, r As Long
r = ActiveCell.Row - 1
c = ActiveCell.Column
For i = 1 To Sheets.Count
Cells(i + r, c).Value = Sheets(i).Name
Next i
End Sub


Gord Dibben MS Excel MVP

On Tue, 1 Sep 2009 05:45:01 -0700, S G Tan
wrote:

When I do that, it defaults to the top of the page and start the list. Is it
possible to change the macro,so that it list the start of the TOC at the cell
that I specify?

"Don Guillett" wrote:

sub toc()
for i=1 to sheets.count
cells(i,1).value=sheets(i).name
next i
end sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"S G Tan" wrote in message
...
I have 15 or more worksheet tabs. How do I make a list/auto-populate of all
the name of the tabs on one worksheet to form the basis of an index?

thanks again - that worked.

"Gord Dibben" wrote:

Sub toc()
Dim i, c, r As Long
r = ActiveCell.Row - 1
c = ActiveCell.Column
For i = 1 To Sheets.Count
Cells(i + r, c).Value = Sheets(i).Name
Next i
End Sub


Gord Dibben MS Excel MVP

On Tue, 1 Sep 2009 05:45:01 -0700, S G Tan
wrote:

When I do that, it defaults to the top of the page and start the list. Is it
possible to change the macro,so that it list the start of the TOC at the cell
that I specify?

"Don Guillett" wrote:

sub toc()
for i=1 to sheets.count
cells(i,1).value=sheets(i).name
next i
end sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"S G Tan" wrote in message
...
I have 15 or more worksheet tabs. How do I make a list/auto-populate of all
the name of the tabs on one worksheet to form the basis of an index?

Could I trouble you again? Is it possible to produce that index and hyperlink
to the relevant tab?

"S G Tan" wrote:

thanks again - that worked.

"Gord Dibben" wrote:

Sub toc()
Dim i, c, r As Long
r = ActiveCell.Row - 1
c = ActiveCell.Column
For i = 1 To Sheets.Count
Cells(i + r, c).Value = Sheets(i).Name
Next i
End Sub


Gord Dibben MS Excel MVP

On Tue, 1 Sep 2009 05:45:01 -0700, S G Tan
wrote:

When I do that, it defaults to the top of the page and start the list. Is it
possible to change the macro,so that it list the start of the TOC at the cell
that I specify?

"Don Guillett" wrote:

sub toc()
for i=1 to sheets.count
cells(i,1).value=sheets(i).name
next i
end sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"S G Tan" wrote in message
...
I have 15 or more worksheet tabs. How do I make a list/auto-populate of all
the name of the tabs on one worksheet to form the basis of an index?

Don has given you code to get a list of sheets.

What do you want to do with this "index"?

You want to be able to select any sheet when you click on a name?

You can build a Sheet Navigation Toolbar as per Dave Peterson's code on
Debra Dalgleish's site. Sorts as well as navigates.

http://www.contextures.on.ca/xlToolbar01.html

Or see this google search thread for the BrowseSheets macro from Bob
Phillips.

http://tinyurl.com/yoa3dw


Gord Dibben MS Excel MVP

On Mon, 31 Aug 2009 14:22:01 -0700, S G Tan
wrote:

I have 15 or more worksheet tabs. How do I make a list/auto-populate of all
the name of the tabs on one worksheet to form the basis of an index?

Many thanks to both of you.

"Gord Dibben" wrote:

Don has given you code to get a list of sheets.

What do you want to do with this "index"?

You want to be able to select any sheet when you click on a name?

You can build a Sheet Navigation Toolbar as per Dave Peterson's code on
Debra Dalgleish's site. Sorts as well as navigates.

http://www.contextures.on.ca/xlToolbar01.html

Or see this google search thread for the BrowseSheets macro from Bob
Phillips.

http://tinyurl.com/yoa3dw


Gord Dibben MS Excel MVP

On Mon, 31 Aug 2009 14:22:01 -0700, S G Tan
wrote:

I have 15 or more worksheet tabs. How do I make a list/auto-populate of all
the name of the tabs on one worksheet to form the basis of an index?