title: Excel VBA Copy/Paste Link from Worksheet
body: <p>I am not sure what is going on here.  At a high level, here is what I am trying to accomplish:</p>
  
  <ol>
  <li><p>In the Worksheet named "LH-to-HousingL1L4 Fastener", find the cell that contains the text "Minimums by LCID"</p></li>
  <li><p>Use the results from (1) above to offset down 2 cells and to the left 1 cell where I will start my selection</p></li>
  <li><p>Use the <code>Selection.End(xlDown)).Select</code> method to select from the cell in (2) above all the way to the end of the data (there are no blanks/gaps)</p></li>
  <li><p>Copy the range selected in (3) above</p></li>
  <li><p>Paste the link from (4) to the range starting at "B3783" downward on a different worksheet called "SuperMargins"</p></li>
  </ol>
  
  <p>When I run the code, it <strong><em>almost</em></strong> works however the links that get pasted are not fully qualified.  That is to say that they look like this: </p>
  
  <p><code>=AX27</code></p>
  
  <p>instead of this: </p>
  
  <p><code>='LH-to-HousingL1L4 Fastener'!AX24</code></p>
  
  <p>How can I get the desired behavior?</p>
  
  <p>Thanks for reading!</p>
  
  <pre><code>Option Explicit
  Sub CopyFastenerMargins()
  
  Dim nameRng As Range
  Dim r As Range
  
  Set r = Worksheets("LH-to-HousingL1L4 Fastener").Cells.Find(What:="Minimums by LCID", Lookat:=xlWhole, MatchCase:=True)
  Set r = r.Offset(2, -1)
  Dim s As String
  s = r.Address
  With Worksheets("LH-to-HousingL1L4 Fastener")
      Range(s).Select
      Range(Selection, Selection.End(xlDown)).Select
      Selection.Copy
  End With
  
  Sheets("SuperMargins").Select
  Range("B3783").Select
  ActiveSheet.Paste Link:=True
  
  End Sub
  </code></pre>