Hyperlink to Original Sheet

Question

I'm running into an issue I'm hoping someone can lend a hand with.

I have an index sheet on my workbook that has an overview of all other sheets in the workbook. I'm attempting to automate creating a hyperlink to the individual sheets on the index sheet.

I've automated the process to the point where the link is created, but I cannot figure out how to have the hyperlink point to the sheet where the information was copied from. Here's what I have so far:

Sub PasteOverview()
With ActiveSheet
Link = Range("D3")
Range("B42:J42").Select
Selection.Copy
Range("A1").Select
Sheets("Active").Select
Cells(Range("B10000").End(xlUp).Row + 1, 2).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveSheet.Paste Link:=True
Range("C" & (ActiveCell.Row)).Select
ActiveSheet.Hyperlinks.Add ActiveCell, "", "Link"
End With
End Sub

I just can't figure out how to have the hyperlink point to the sheet the information was copied from. Any help would be greatly appreciated.

EDIT: Re-reading through my question I see the wording was poor, as it was unclear if I wanted to hyperlink to the last active sheet or the sheet where the information was originally copied from. I have reworded the question to clarify.

I would suggest qualifying exactly which sheet you are calling the Range and Cells methods on. I don't think your With block actually does anything at the moment. It would be much easier to link the sheet if you set it to a variable and used that variable to qualify your methods as I said earlier. You can use [this link](https://stackoverflow.com/questions/25871416/excel-fully-qualifying-range-cells-cells) as a start to see what I am talking about. — user2731076, Jan 18 '18 at 15:46

score 0 · Answer 1 · answered Jan 18 '18 at 15:46

Give this a shot:

Option Explicit

Sub PasteOverview()

    Dim wsCopy As Worksheet
    Set wsCopy = ActiveSheet

    With wsCopy

        Dim link As String
        link = .Range("D3").Address(False, False)
        .Range("B42:J42").Copy

    End With

    With Worksheets("Active")

        Dim nextRow As Long
        nextRow = .Range("B10000").End(xlUp).Offset(1).Row
        .Range("B" & nextRow).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        ActiveSheet.Paste link:=True
        .Hyperlinks.Add .Range("C" & nextRow), "", wsCopy.Name & "!" & link, wsCopy.Name & "!" & link

    End With

End Sub

which line? @EHemsing – Scott Holtzman Jan 18 '18 at 16:20 — Scott Holtzman, Jan 18 '18 at 16:20

score 0 · Answer 2 · answered Jan 18 '18 at 15:46

You could define a Range Name for the linked area, Range("B42:J42").Name = "Link". The rest of the code is the same:

Sub PasteOverview()
    With ActiveSheet
        Link = Range("D3")
        Range("B42:J42").Select

        Range("B42:J42").Name = "Link"

        Selection.Copy
        Range("A1").Select
        Sheets("Active").Select
        Cells(Range("B10000").End(xlUp).Row + 1, 2).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        ActiveSheet.Paste Link:=True
        Range("C" & (ActiveCell.Row)).Select
        ActiveSheet.Hyperlinks.Add ActiveCell, "", "Link"
    End With
End Sub

You will, though, need to make the range names unique for all the areas you wish to link to. The distinct names of the sheets themselves can help to achieve this.

Hyperlink to Original Sheet

2 Answers2