Dynamic Hyperlinks
 

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

Dynamic Hyperlinks
 

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

--
Regards
Roger Govier

"Noob McKnownowt" wrote in
message ...
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

Dynamic Hyperlinks
 

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

Noob

"Roger Govier" wrote:

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

--
Regards
Roger Govier

"Noob McKnownowt" wrote in
message ...
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