title: VBA how to pass named range Name into a formula rather than a cell content
body: <p>I have 3 ranges, "AA", "AB", and "AC". </p>
  
  <p>The <em>names</em> of these ranges are on sheet 1 in column A.. so....</p>
  
  <p>Cell A1 contains two letters "AA"
  Cell A2 contains AB
  Cell A3 contains AC</p>
  
  <p>I need a formula to go down this list (A1:A3) and pull in the names of the ranges and paste them on another sheet. </p>
  
  <p>I am able to achieve this with this code:</p>
  
  <pre><code>Range("AA").Copy Sheets(2).Range("B6")
  </code></pre>
  
  <p>Now I need a way to substitute the "AA" for a dynamic range such as Cell(c,r) so the formula can go down the list...because the names of these named ranges will change in other spreadsheets so I can't hardcode them. </p>
  
  <p>Thank you for any suggestions!!!</p>