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?

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?

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?

See David McRitchie's site for code to build a Table of Contents with
hyperlinks.

http://www.mvps.org/dmcritchie/excel...c.htm#BuildTOC

I still prefer the sheet navigator toolbar or browsesheets macro.

With a TOC how do you go back to TOC sheet after jumping to another sheet?


Gord


On Tue, 1 Sep 2009 12:37:01 -0700, S G Tan
wrote:

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?

Just use a double click event instead on the name typed into the cell.
Right click sheet tabview codeinsert this

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Application.DisplayAlerts = False
Dim WantedSheet As String
WantedSheet = Trim(ActiveCell.Value)
If WantedSheet = "" Then Exit Sub
On Error Resume Next
If Sheets(WantedSheet) Is Nothing Then
'GetWorkbook ' calls another macro to do that
Else
Application.Goto Sheets(WantedSheet).Range("a4")
End If
Application.DisplayAlerts = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"S G Tan" wrote in message
...
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?

Worked like a treat -you guys are amazing. Many thanks

"Don Guillett" wrote:

Just use a double click event instead on the name typed into the cell.
Right click sheet tabview codeinsert this

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Application.DisplayAlerts = False
Dim WantedSheet As String
WantedSheet = Trim(ActiveCell.Value)
If WantedSheet = "" Then Exit Sub
On Error Resume Next
If Sheets(WantedSheet) Is Nothing Then
'GetWorkbook ' calls another macro to do that
Else
Application.Goto Sheets(WantedSheet).Range("a4")
End If
Application.DisplayAlerts = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"S G Tan" wrote in message
...
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?