Hi, after failing dismally trying to use =hyperlink, i am looking for a solution which will loop through a number of cells, and add a hyperlink to each cell pulling the sheet name from a named range. i will trigger this as an on open event. The aim of this is to provide dynamic links from a summary sheet which pulls information based on teams where members which change frequently.
my sheet names are stored in the ranges bteam 1 - 10 j team 1 - 10 and mteam 1 - 10
I tried the below which did not work where am i going wrong?
Thanks,
Dan.
my sheet names are stored in the ranges bteam 1 - 10 j team 1 - 10 and mteam 1 - 10
I tried the below which did not work where am i going wrong?
Code:
Private Sub Workbook_Open()
Dim summary As Worksheet
Set summary = Sheets("summary")
summary.Activate
summary.Hyperlinks.Delete ' remove existing hyperlinks.
For i = 10 To 1
With summary
.Hyperlinks.Add anchor:=summary.Cells(i + 3, 2), Address:=Sheets(bteam & i.value)), TextToDisplay:="bteam" & i
.Hyperlinks.Add anchor:=summary.Cells(i + 3, 8), Address:=Sheets(Range(jteam & i.value).Value)), TextToDisplay:="jteam" & i
.Hyperlinks.Add anchor:=summary.Cells(i + 3, 14), Address:=Sheets(Range(mteam & i.Value)), TextToDisplay:="mteam" & i
End With
Next i
End Sub
Thanks,
Dan.