Copy template worksheet and rename the copied worksheets from a list. If worksheet name exists, create a copy

[limemeup]

This is my first post and this website forum has been such an incredible help! Looking for some assistance on copying a template worksheet and renaming the copied worksheets from a list from a range from another sheet. If the worksheet name already exists, it will create a copy stating "Copy" at the end.

So far I was able to create the copies from a list but it did not stop after cell B50 (started from B3).....

• Sh1 = Template sheet is named "xTemplate"
• Sh2 = Sheet with the running list of names is the sheet named "xRunning List"
• The Cell Range from the sheet named "xRunning List" is between Rows B3 to B50.
  • There are some sheets already created that is in between rows B3 to B50 so I was hoping if there is already a sheet with that name from these rows, it would create a duplicate reading "Copy" at the end of the new copied sheet.

Sub makeSheets()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets("xTemplate")
Set sh2 = Sheets("xRunning List")
    For Each c In sh2.Range("B3", sh2.Cells(Rows.Count, 2).End(xlUp))
        sh1.Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = c.Value
    Next
End Sub

Thank you all for the help already in the past (been look up fan) and all the help in the future!

 

[AlphaFrog]

Would you ever have a situation where you create more than one "Copy" of a sheet (e.g. you run the macro multiple times)? If yes, would you not create a 2nd copy, or name it Copy 2, or what?

If the sheet name already exists, do you want a Copy of the template or a copy of the existing sheet?

 

[limemeup]

@AlphaFrog , I will be only running it once but the book has currently a working document being used. I'm looking to do a one time prepopulate of new sheets..

Currently, their are multiple sheets already named with random numbers that corresponds to the list within the "xRunning List" sheet. When I run the code above, I receive this "Run-time error '1004': That name is already taken. Try a different one." I do already know this is because there are multiple existing sheet with the same name that this code is trying to copy. I was hoping the code would just create a copy instead of pausing/ending the code at the sheet that has the already taken name. If it can continue and the "already taken" name just has a "copy", I can transfer the information over from the existing sheet to the newly create sheet (which has been updated as a different format).

"Sorry the above is for B3 to B500" so restarting the VBA multiple times starting at where the error occurs seems to be not the most effective.