Hyperlinked Table of Contents

[golclub]

Hi all

Could you please help me. I need a macro which would create a hyperlinked Table of Contents on Sheet 1 with names taken from the first cell of each sheet. For example A1 of Sheet 2 has heading Canada, A1 of Sheet 3 - America, A1 of Sheet 4 - Mexico, etc.

So the hyperlinked Table of Contents on Sheet 1 would look like

Canada
America
Mexico
..........
.........

untill the name from the A1 of the last sheet.

After that if a user clicks any hyperlink from the table of contents the macro would open the corresponding sheet.


Thanks a lot!

 

[bensonsearch]

Hi all

Could you please help me. I need a macro which would create a hyperlinked Table of Contents on Sheet 1 with names taken from the first cell of each sheet. For example A1 of Sheet 2 has heading Canada, A1 of Sheet 3 - America, A1 of Sheet 4 - Mexico, etc.

So the hyperlinked Table of Contents on Sheet 1 would look like

Canada
America
Mexico
..........
.........

untill the name from the A1 of the last sheet.

After that if a user clicks any hyperlink from the table of contents the macro would open the corresponding sheet.


Thanks a lot!

Hi,
Is there a fixed number of sheets? or does it change alot?

 

[villy]

Try this it works for me...

Private Sub Worksheet_Activate()
    For i = 2 To Sheets.Count
        Set TOC = Range("A" & i)
 
         TOC.Hyperlinks.Add Anchor:=TOC, Address:="", SubAddress:= _
        Sheets(i).Name & "!" & "A1", TextToDisplay:=Sheets(i).Range("A1").Text
    Next
End Sub

 

[golclub]

Thank you Villy!

@bensonsearch: Sheets can be added or removed.

 

[villy]

Thank you Villy!

@bensonsearch: Sheets can be added or removed.

Is that met your requirements?
If so, then glad I can help in my little own way you're welcome.
God bless

 

[golclub]

Yes, Villy. Thanks for the great macro!