title: Dynamically copy a worksheet multiple times and rename using VBA in Excel
body: <p>I am trying to dynamically generate a custom number of worksheets based on a template that we use regularly in excel using VBA. </p>
  
  <p>I have created an "Overview" page where we can input a range which will be used to name the new worksheets but then would like to use a hidden "Master" worksheet to generate the content of these new worksheets.</p>
  
  <p>My code below currently generates the correct number of pages based on the range AND copies our master template page but does not combine the two and leaves them in separate pages.</p>
  
  <pre><code>Sub test()
  Dim MyNames As Range, MyNewSheet As Range
  
  Set masterSheet = ThisWorkbook.Worksheets("Master")
  Set MyNames = Range("A1:A6").CurrentRegion  ' load range into variable
  
  For Each MyNewSheet In MyNames.Cells    ' loop through cell range
      masterSheet.Copy ThisWorkbook.Sheets(Sheets.Count) 'copy master template sheet
      Sheets.Add.Name = MyNewSheet.Value
  Next MyNewSheet
  
  MyNames.Worksheet.Select    ' move selection to original sheet
  End Sub
  </code></pre>
  
  <p>As you can see, the code generates both the named (blank) worksheets AND copies my master worksheet which defaults to naming as "Master()".
  <a href="https://i.stack.imgur.com/ymSb7.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ymSb7.png" alt="enter image description here"></a></p>