Yes, all twelve single celled range names exist in the template worksheet.
Furthermore, so code knows where to go to get the various data values to transfer into the copy of the template workbook, I added twelve names in the Master Roster, one each for the column HEADERs from which data is transferred. For example, the column header for first name is named Header_FirstName. Also the column header for initials is named Header_Initial.
One small complicating factor is "Sheet-scoped" names. That is, you can define a name whose "scope" 1) is the entire workbook OR 2) a specific to a worksheet. All names I added are worksheet scoped names.
I also used "code names" for the workbooks. That is good for at least one reason: if the sheet name/tab changes the code still refers to the correct worksheet. I'm sure that that sounds complicated but once you get the hang of it it is easy and a good way to do programming when referring to worksheets. In code a worksheet is referred to like this [Template] where the code name is Template.
See
HERE for articles about code names.
All that said, the workbook that I put together -- based on what you provided -- is
HERE.
Let me know if it does what is needed.
VBA Code:
Option Explicit
Sub DeleteExistingSheets()
Dim wsCurrentSheet As Worksheet
Dim sRangeRefersTo As String
For Each wsCurrentSheet In ThisWorkbook.Worksheets
sRangeRefersTo = ""
On Error Resume Next
sRangeRefersTo = Right(wsCurrentSheet.Names("IsTemplate").RefersTo, 4)
On Error GoTo 0
If sRangeRefersTo = "TRUE" And wsCurrentSheet.CodeName <> "Template" _
Then
Application.DisplayAlerts = False
wsCurrentSheet.Delete
End If
Next
End Sub
VBA Code:
Option Explicit
Sub CreateSheets()
Dim wsNewSheet As Worksheet
Dim iOccupiedRows As Long
Dim iRow As Long
Dim iExistingSheetsCount As Long
Dim sInitials As String
With [MasterRoster].Range("Header_Initials").Offset(1)
Do While .Cells(iOccupiedRows).Offset(1) <> ""
iOccupiedRows = iOccupiedRows + 1
Loop
End With
If iOccupiedRows > 0 _
Then
Call DeleteExistingSheets
iExistingSheetsCount = ThisWorkbook.Worksheets.Count
For iRow = 1 To iOccupiedRows
sInitials = [MasterRoster].Range("Header_Initials").Offset(iRow).Value
[Template].Copy After:=Sheets(iExistingSheetsCount - 1)
iExistingSheetsCount = iExistingSheetsCount + 1
With ActiveSheet
.Name = sInitials
.Range("FirstName").Value = [MasterRoster].Range("Header_FirstName").Offset(iRow).Value
.Range("LastName").Value = [MasterRoster].Range("Header_LastName").Offset(iRow).Value
.Range("MiddleName").Value = [MasterRoster].Range("Header_MiddleName").Offset(iRow).Value
.Range("Address").Value = [MasterRoster].Range("Header_Address").Offset(iRow).Value
.Range("City").Value = [MasterRoster].Range("Header_City").Offset(iRow).Value
.Range("State").Value = [MasterRoster].Range("Header_State").Offset(iRow).Value
.Range("ZipCode").Value = [MasterRoster].Range("Header_ZipCode").Offset(iRow).Value
.Range("PhoneNumber").Value = [MasterRoster].Range("Header_PhoneNumber").Offset(iRow).Value
.Range("EmailAddress").Value = [MasterRoster].Range("Header_EmailAddress").Offset(iRow).Value
.Range("Position").Value = [MasterRoster].Range("Header_Position").Offset(iRow).Value
.Range("TeamNumber").Value = [MasterRoster].Range("Header_TeamNumber").Offset(iRow).Value
.Range("TagNumber").Value = [MasterRoster].Range("Header_TagNumber").Offset(iRow).Value
End With
Next iRow
End If
End Sub