title: Loop through range, find value in separate worksheet, copy values and return and paste
body: <p>I have a recorded macro that I want to make more intelligent and maintenance free. The macro is very specific in that cell addresses and values are hard-coded. If the list of values changes, then the macro code has to be edited. Not a problem for me, but future users will likely not have the knowledge or inclination to do it.</p>
  <p>I think a loop would be the best solution, but am not certain how to proceed.</p>
  <p>Here's the current version of the code:</p>
  <pre><code>    '======= APPROVED
      Sheets(&quot;Worksheet&quot;).Visible = True  -- Unhide the &quot;Worksheet&quot; tab
      Sheets(&quot;Worksheet&quot;).Select
      Range(&quot;B8&quot;).Select                  -- go to cell B8
      ActiveCell.FormulaR1C1 = &quot;Approved&quot; -- type &quot;Approved&quot;
  
  'C9755
  'Expense and Lease Current Year
      Range(&quot;B2&quot;).Select                  -- Go to cell for search value
      ActiveCell.FormulaR1C1 = &quot;C9755&quot;    -- Type in &quot;C9755&quot; (first item)
      Range(&quot;I1:T1&quot;).Select               -- Copy the total in the range
      Selection.Copy
      Sheets(&quot;Summary by Component&quot;).Select -- Go back to other tab
      Range(&quot;C9&quot;).Select                    -- This is where &quot;C9755&quot; is
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
  'Capital Depreciation
      Sheets(&quot;Worksheet&quot;).Select    -- Now go back and do the same for Capital
      Range(&quot;I2:T2&quot;).Select
      Selection.Copy
      Sheets(&quot;Summary by Component&quot;).Select
      Range(&quot;C35&quot;).Select   -- This is where &quot;C9755&quot; Capital is
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
  </code></pre>
  <p>Picture of flow:
  <a href="https://i.stack.imgur.com/dh6Gp.png" rel="nofollow noreferrer">Flow</a></p>