title: Get Named Range Values
body: <p>I have two workbooks that have a lot of the same named ranges and I need to move values from the second workbook to the first. I can do this by specifying which sheet the range is on and the name of range, but there are about 200 named ranges spread out over 20-something sheets and I only need the named ranges that are on 11 of those sheets. I could take the time to figure it out and hard code moving each one, but would really like it to be a mostly automated process. That way if a new range is added it can just be done automatically and I don't have to update the code.</p>
  
  <p>The code I have so far is:</p>
  
  <pre class="lang-vb prettyprint-override"><code>For Each Name In sheetList
      Set sheet2vals = sheet2.Sheets(Name).Range(rangename)
      Set sheet1vals = sheet1.Sheets(Name).Range(rangename)
      sheet2vals.Copy sheet1vals
  Next Name
  </code></pre>
  
  <p>sheetList is an array of sheet names where all the ranges are stored. That part works exactly as I need it, I just need to find a way to pull the ranges from each of the sheets and move them. However, the only way I can seem to find to get the list of named ranges is by pulling at the Workbook level, which doesn't tell me what sheet it is on.</p>