title: VB Macro to create new workbooks and prefill the new workbooks from the master sheet
body: <p>I have a macro which works as follows:</p>
  
  <p>I have a workbook which has four tabs in it, 
  *Index - Contains a list of Building Information ( 5 Columns in total starting from A finishing E, some months there can be 3 rows and other months there could be 100 rows
  *Template - a template page with basic building info
  *Data - A checklist
  *B2 - Another checklist</p>
  
  <p>what I need the macro to do is to create a new workbook which contains worksheets "Template" "Data" and "B2"</p>
  
  <p>The new workbooks need to be named in accordance with the building name contained in column A on the index worksheet</p>
  
  <p>I then need the list located on the index tab to copy to cells F2, F3, F4, F5, F6 F5 on the template tab for each new workbook ( one for each header )</p>
  
  <p>Below is the code which works but currently only prefills to cell F2 on the template worksheet in the new workbook</p>
  
  <pre><code>    Sub temp()
  Dim wb As Workbook, sh1 As Worksheet, lr As Long, rng As Range
  Set sh1 = Sheets("Index") 'Edit sheet name
  Set sh2 = Sheets("B2") 'Edit sheet name
  Set sh3 = Sheets("Data") 'Edit sheet name
  lr = sh1.Cells(Rows.Count, "A").End(xlUp).Row
  Set rng = sh1.Range("A2:A" &amp; lr)
      For Each c In rng
          Sheets("Template").Copy 'Edit sheet name
          Set wb = ActiveWorkbook
          wb.Sheets(1).Range("F2") = c.Value
          sh2.Copy After:=wb.Sheets(1)
          sh3.Copy After:=wb.Sheets(2)
          wb.SaveAs c.Value &amp; ".xlsx"
          wb.Close False
      Next
  End Sub
  </code></pre>
  
  <p>Thanks heaps... im sure I have got 90% of this done just the finishing touches </p>