title: VBA - copy different template sheets from a workbook, into multiple sheets of another workbook based on criteria on a summary excel sheet
body: <p>I'm pretty new to VBA (3 days of exp), I have had a look through several forums but I can't find the solution.</p>
  
  <p>I have 2 workbooks. The "master" workbook has a summary sheet with column A - List of names hyperlinked to a blank sheet each in the same workbook, the tabs are labelled the same as the name in the column. Column B has 1 or a combination of colour - there is 5 options (red, blue, green, blue &amp; red, or red &amp; green). 
   I have a separate template workbook that has 5 template sheets each one corresponding to the colour: labelled red, blue, green, blue &amp; red, or red &amp; green. </p>
  
  <p>I want a macro that will go through column B of my "master" workbook, and depending on the colour, copy the corresponding template from the template workbook and then go back to the master workbook click through the link in the adjacent column A, which will take it through to an empty sheet and paste the template. This should repeat to go through the entire column.</p>
  
  <p>For example :</p>
  
  <ol>
  <li>Recognises that Cell B2 in "master" workbook has the colour red. </li>
  <li>Opens the template workbook, </li>
  <li>go to the sheet labelled red </li>
  <li>copy entire sheet</li>
  <li>Go back to "master" workbook</li>
  <li>click on the hyperlinked name in the cell (A2) next to B2</li>
  <li>This will take you to a blank sheet</li>
  <li>Paste the template</li>
  <li>Go back to "Master" workbook and repeat for the rest of the column </li>
  <li>If its red again, then do the same, if a different colour like blue, then copy paste the blue template sheet. </li>
  </ol>
  
  <p>I have tried to write a code myself from what was available in other forums, but it only copy pastes onto the first 2 sheets of the "Master" workbook out of 10 sheets that requires the red template. I have only written it for 1 colour criteria so far since no point in adding multiple criteria if 1 isn't working:</p>
  
  <pre><code>Sub Summary()    
  Dim rng As Range    
  Dim i As Long    
  Set rng = Range("B:B")   
  For Each cell In rng       
  If cell.Value &lt;&gt; "Red" Then cell.Offset(0, -1).select 
  ActiveCell.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
  Workbooks.Open Filename:= _
      "T:\Contracts\Colour Templates.xlsx"
  
  
  Sheets("Red Template").Select
  Cells.Select
  Selection.Copy
  Windows("Master.xlsx").Activate
  ActiveSheet.Range(“A1”).select
  
  ActiveSheet.Paste
  Next
  End Sub
  </code></pre>