Home » Questions » Computers [ Ask a new question ]

automatic hyperlink to worksheets in excel 2007

automatic hyperlink to worksheets in excel 2007

I have a worksheet called 'summary' this contains a list of english counties, and I then have a seperate worksheet for each county.

Asked by: Guest | Views: 98
Total answers/comments: 1
Guest [Entry]

"You could use the HYPERLINK in conjunction with the CELL function and some string functions. Or you could use the following macro:

Sub LinkToSheet()

Dim Rng As Range
Dim maxRows, r As Integer

Set Rng = Selection

maxRows = Rng.Rows.Count 'number of rows in the selection
r = 1

Do While r <= maxRows
ActiveSheet.Hyperlinks.Add Anchor:=Rng(r, 1), Address:="""", SubAddress:=Rng(r, 1).Value & ""!A1"", TextToDisplay:=Rng(r, 1).Value
On Error Resume Next
r = r + 1
Loop

End Sub

Usage: Highlight all the country names in your summary sheet, press Alt+F8 and then double-click the macro. This assumes that the sheets in your list exist. The macro still creates the hyperlink even for a sheet that does not exist. It also skips blank cells. Here's a sample worksheet: http://ge.tt/2gheiw5"