Using VBA - Hyperlink where hyperlink is variable (based on Address & Match formula)
<p>I'm hoping someone can help me, as I'm well and truly stuck.</p>

<p>I am trying to write a Macro which would follow a hyperlink to a cell in another spreadsheet.  The reason I've chosen to use a hyperlink is that I need the cell reference to be variable.</p>

<p>The formula I've written is as follows:</p>

<p><code>=HYPERLINK("#'Label Editor'!"&amp;ADDRESS(MATCH(A1,'Label Editor'!$B$1:$B$1001,0),3),"Go to label")</code></p>

<p>The formula works fine - however the problem is when I attempt to record a macro.  Instead of following the hyperlink, it records a fixed cell reference as below:</p>

<pre><code>Sub Test1()
'
' Test1 Macro
'
' Keyboard Shortcut: Option+Cmd+q
'
    Range("G1:I1").Select
    **Application.Goto Reference:="'Label Editor'!R125C3"**
    Selection.Copy
    Sheets("5x13").Select
    Range("A2:A22").Select
    ActiveSheet.Paste
End Sub
</code></pre>

<p>I've tried to find a way to integrate the hyperlink within the macro, but so far to no avail.  I've tried replacing it with a Lookup formula as follows:</p>

<pre><code>**Application.Goto Reference:=Application.VLookup([A1], [Label Editor], [$b$1:c$1000], 2, False)**
    Selection.Copy
    Sheets("5x13").Select
    Range("A2").Select
    ActiveSheet.Paste
    Range("I2:I254,G2:G254,E2:E254,C2:C254,A2:A254").Select
    Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
End Sub
</code></pre>

<p>However this comes up with: Run-time error '1004':</p>

<p>I'm now completely stuck - can anyone help?</p>