Dynamic Hyperlinks

[Noob McKnownowt]

Hi Guys, this is the first question i have posted and as my name suggests i'm
a noob

i have this problem, i am trying to create a workbook that logs the mistakes
that could be made on a given day at work by any given employee.

i have 2 worksheets the first is a summary page that holds the names of the
employees and the second is a template that the sheets for each employee are
derived from.

i have written a little VBA sub that will look at the list and create a new
worksheet (copy and then rename the template) for each employee on the list,
the problem is, is that i want the employee names on the summary page to
become hyperlinks to their corrisponding worksheet, this is the code i have
written so far;

Sub HyperLink()

Dim WkSh As Worksheet
Dim sze As Long
Dim i As Long

i = 4
sze = Sheets("Summary").Cells(Rows.Count, 3).End(xlDown).Row

For Each WkSh In Worksheets
While sze > i
If WkSh.Name = Sheets("Summary").Cells(i, 3).Value Then
Sheets("Summary").Cells(i, 3).Select
WkSh = WkSh.Name
Sheets("Summary").Cells(i, 3).Hyperlinks.Add Anchor:=Selection,
Address:="", SubAddress:= _
WkSh, TextToDisplay:=Sheets("Summary").Cells(i, 3).Value
End If
i = i + 1
Wend
i = 4
Next WkSh

End Sub

Any assistance that anyone could provide would be very much apprieciated,
thanks

The Noob

 

[Roger Govier]

Hi

I would use something like the following, to read through each name in
column A, starting at A4, and adding the hyperlinks

Sub InsertHyperlinks()
Dim i As Long, lr As Long
lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
For i = 4 To lr
Cells(i, 1).Hyperlinks.Add Anchor:=Cells(i, 1), Address:="",
SubAddress:= _
Cells(i, 1).Text & "!A1", TextToDisplay:=Cells(i, 1).Text
Next i
End Sub

Add your own additional tests if required

 

[Noob McKnownowt]

Thanks alot matey, will test later, but thanks for the suggestion i was
getting annoyed.

Noob