Table of contents with hyperlinks to worksheets, plus "home" hyperlink

[Harry Flashman]

I would like to find a way to insert a "Home" hyperlink on all
worksheets in a workbook. The hyperlink should link to Sheet1!A1 (my
table of contents worksheet).
Presently I have to enter the hyperlink one a time on each worksheet.
Is there a way I can do this in one go?

Some background information:
I am trying to automate the process of setting up my workbooks. (I
have nearly worked out how to do this).
Sheet 1 should be the table of contents TOC.
The rest of the sheets: one worksheet per item in the TOC, with a
hyperlink to each worksheet from the TOC.

This macro creates worksheets based on my TOC. (Found this macro on
the internet somewhere, maybe this newsgroup - my thanks to the
author).

Sub newws()
Dim arr As Variant
arr = Selection.Value
For i = LBound(arr) To UBound(arr)
Set NewSheet = Sheets.Add
NewSheet.Name = arr(i, 1)
Next i
End Sub

The only downside to this macro is that the worksheets end up in the
reverse order of how I would like them to be; however, I get around
this by temporarily reversing the order of the table of contents. Is
it possible to alter this macro in some way so that I don't have to do
this. (Perhaps so that it creates the last item first and then works
its way up the TOC list instead of down the TOC).

The next macro I found at this page:
http://www.mvps.org/dmcritchie/excel/buildtoc.htm

This makes each item in my TOC a hyperlink to the relevant worksheet.

Sub trevor001()
Dim Cell As Range
For Each Cell In Selection
ActiveSheet.Hyperlinks.Add Anchor:=Cells(Cell.Row, Cell.Column), _
Address:="", SubAddress:="'" & Sheets(Cell.Value).Name &
"'!A1"
Next Cell
End Sub

So, I am nearly there; I just need to insert a homepage hyperlink on
each page. Any suggestions.

Regards,
Harry

 

[Dave Peterson]

Option Explicit
Sub newws()
Dim arr As Variant
Dim i As Long
Dim NewSheet As Worksheet
arr = Selection.Value
For i = LBound(arr) To UBound(arr)
Set NewSheet = Sheets.Add
NewSheet.Name = arr(i, 1)
With NewSheet.Range("a1")
.Hyperlinks.Add anchor:=.Cells, Address:="", _
SubAddress:="'sheet1'!a1"
End With
Next i
End Sub

 

[Harry Flashman]

Option Explicit
Sub newws()
Dim arr As Variant
Dim i As Long
Dim NewSheet As Worksheet
arr = Selection.Value
For i = LBound(arr) To UBound(arr)
Set NewSheet = Sheets.Add
NewSheet.Name = arr(i, 1)
With NewSheet.Range("a1")
.Hyperlinks.Add anchor:=.Cells, Address:="", _
SubAddress:="'sheet1'!a1"
End With
Next i
End Sub
















--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks very much, your macro creates the worksheets and inserts the
home hyperlink. I can use the second macro that I mentioned to convert
the table of contents into hyperlinks. I really appreciate your help.
This will come in very handy.