Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / September 2007

Tip: Looking for answers? Try searching our database.

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

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Harry Flashman - 06 Sep 2007 09:47 GMT
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.

Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.