add hyperlinks to a range

[hocs]

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]

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]

Try

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]

Great! Thanks VoG!
nrow=3 will get what i want! (link starts from D4 onwards!)

Cheers!