Hyperlink from cell to sheet

[niklasbp]

I have made makro with a index sheet named "Rapport" that has column A with a list of names starting at A2 going down. Each of these names has a sheet in their name attached and now i want to make a link from each name to the sheet with their name on it.

I managed to make the names into links but i cannot get the reference correct therefore i think there is a problem with the subadress

The code i have used so far looks like this:

Sub CreateHyperlinks()

Dim finalSheet As String
Dim myRange As Excel.Range
Dim cell As Excel.Range
Set myRange = Excel.ThisWorkbook.Sheets("Rapport").Range("A1:A100")

For Each cell In myRange
Excel.ThisWorkbook.Sheets("Rapport").Hyperlinks.Add Anchor:=cell, Address:="", SubAddress:=cell.Value &"!A1"
Next cell

EndSub

can someone please help me with this =D?

/niklas

[SamT]

IIRC

Address:="'" & Cell.Value & "'!A1", TextToDisplay:=Cell.value

"'" = DoubleQuote+SingleQuote+DoubleQuote

[Paul_Hossler]

The macro recorder is your friend (Macro1 and Macro2)



Option Explicit


Sub Macro1()
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="Tom!A1", TextToDisplay:="Tom"
End Sub

Sub Macro2()
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'Mary Smith'!A1", TextToDisplay:="Mary Smith"
End Sub


Sub CreateHyperlinks()
Dim finalSheet As String
Dim myRange As Range
Dim myCell As Range

Set myRange = ThisWorkbook.Sheets("Rapport").Range("A1:A100")

For Each myCell In myRange.Cells
If Len(myCell.Value) > 0 Then
On Error Resume Next
myCell.Hyperlinks(1).Delete
On Error GoTo 0
ThisWorkbook.Sheets("Rapport").Hyperlinks.Add Anchor:=myCell, Address:="", SubAddress:="'" & myCell.Value & "'!A1", TextToDisplay:=myCell.Value
End If
Next
End Sub



If you use the [#] button and paste your macro in between the [ CODE ] and [ /CODE ] tags it looks more readable

I changed 'Cell' to myCell and added a little error checking. This assumes that all the right sheets are there otherwise you get a "Reference Not Valid" when you click on the link