add hyperlinks to a range

hocs

New Member
Joined
Nov 13, 2009
Messages
33
Hi,
i need code to add hyperlink to a range, says D4:D53, to refer to sheet 4 to sheet 53.
Note: sheets name will change but i want the link to always refer to sheet 4 to sheet 53. i.e. valid links irregards to sheets names.

Thanks!
 

hocs

New Member
Joined
Nov 13, 2009
Messages
33
Hi,
i need code to add hyperlink to a range, says D4:D53, to refer to sheet 4 to sheet 53.
Note: sheets name will change but i want the link to always refer to sheet 4 to sheet 53. i.e. valid links irregards to sheets names.

Thanks!
i have got code which is close to what i want but need some modification:

Sub InsertHyperlinks()
Application.ScreenUpdating = False
Dim ws As Worksheet, _
shtName As String, _
nrow As Long, _

nrow = 0

For Each ws In ActiveWorkbook.Worksheets (i only want it to start from sheet 4 to the last sheet)
nrow = nrow + 1
With ws
shtName = ws.Name
With Sheets("INVOICE SUMMARY")
.Range("D" & nrow).Hyperlinks.Add _
Anchor:=Sheets("INVOICE SUMMARY").Range("D" & nrow), Address:="#'" & _
shtName & "'!A1", TextToDisplay:=shtName
End With
End With
Next ws

End Sub

This will insert hyperlinks for all sheets into sheet "INVOCIE SUMMARY" start from D1. However i want to ignore the 1st 3 sheets i.e. to start insert hyperlink for sheet 4 into cell D4 of INVOICE SUMMARY until the last worksheets.
Please advise.
Thanks!
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
Try

Code:
Sub InsertHyperlinks()
Application.ScreenUpdating = False
Dim ws As Worksheet, shtName As String, nrow As Long, i As Integer
nrow = 0
For i = 4 To ActiveWorkbook.Sheets.Count
    Set ws = ActiveWorkbook.Sheets(i)
    nrow = nrow + 1
    With ws
        shtName = ws.Name
        With Sheets("INVOICE SUMMARY")
            .Range("D" & nrow).Hyperlinks.Add _
                Anchor:=Sheets("INVOICE SUMMARY").Range("D" & nrow), Address:="#'" & _
                shtName & "'!A1", TextToDisplay:=shtName
        End With
    End With
Next i
End Sub
 

hocs

New Member
Joined
Nov 13, 2009
Messages
33
Great! Thanks VoG!
nrow=3 will get what i want! (link starts from D4 onwards!)

Cheers!
 

Forum statistics

Threads
1,085,335
Messages
5,383,032
Members
401,812
Latest member
topherj09

Some videos you may like

This Week's Hot Topics

Top