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 - 06 Sep 2007 12:35 GMT
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
> 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
[quoted text clipped - 48 lines]
> Regards,
> Harry

Signature
Dave Peterson
Harry Flashman - 06 Sep 2007 13:43 GMT
> Option Explicit
> Sub newws()
[quoted text clipped - 70 lines]
>
> - 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.