title: Reference last row of different sheet
body: <p>currently I have this on Sheet2</p>
  
  <pre><code>Range("A2").Select
  Selection.AutoFill Destination:=Range("A2:A1001")
  Range("A2:A1001").Select
  Range("B2").Select
  Selection.AutoFill Destination:=Range("B2:B1001")
  Range("B2:B1001").Select
  Range("C2").Select
  Selection.AutoFill Destination:=Range("C2:C1001")
  Range("C2:C1001").Select
  Range("D2").Select
  Selection.AutoFill Destination:=Range("D2:D1001")
  Range("D2:D1001").Select
  Range("E2").Select
  Selection.AutoFill Destination:=Range("E2:E1001")
  Range("E2:E1001").Select
  Range("G2").Select
  Selection.AutoFill Destination:=Range("G2:G1001")
  Range("G2:G1001").Select
  Range("H2").Select
  Selection.AutoFill Destination:=Range("H2:H1001")
  Range("H2:H1001").Select
  Range("I2").Select
  Selection.AutoFill Destination:=Range("I2:I1001")
  Range("I2:I1001").Select
  Range("J2").Select
  Selection.AutoFill Destination:=Range("J2:J1001")
  Range("J2:J1001").Select
  Range("K2").Select
  Selection.AutoFill Destination:=Range("K2:K1001")
  Range("K2:K1001").Select
  </code></pre>
  
  <p>Instead of having the destination range A2 to A1001 I want to have A2 to the same last row as sheet1 e.g if in sheet1 the last row is row 147 I want the code to fill down <code>Selection.AutoFill Destination:=Range("A2:A147")</code></p>
  
  <p>I do not know how to accomplish this.</p>
  
  <p>Thanks</p>