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
' Look for the name IsTemplate in the sheet to determine if it is one to delete.
' The Template sheet and copies thereof have a name whose RefersTo = "=TRUE".
' If the sheet being processed is the Template sheet or a copy thereof Then
' the right four characters of the RefersTo for the name IsTemplate = "TRUE".
sRangeRefersTo = ""
On Error Resume Next
sRangeRefersTo = Right(wsCurrentSheet.Names("IsTemplate").RefersTo, 4)
On Error GoTo 0
' If the sheet is a COPY of the Template worksheet then delete it.
If sRangeRefersTo = "TRUE" And wsCurrentSheet.CodeName <> "Template" _
Then
' Don't ask user if it is ok to delete a worksheet.
Application.DisplayAlerts = False
' Delete the worksheet.
wsCurrentSheet.Delete
End If
Next
End Sub
VBA Code:
Option Explicit
Sub CreateSheets()
' Worksheet object will point to the worksheet being processed.
Dim wsNewSheet As Worksheet
' Count of occupied rows in the Master Roster worksheet.
Dim iOccupiedRows As Long
' Used to iterate/loop through names found.
Dim iRow As Long
' Get count of all worksheets in the workbook.
Dim iExistingSheetsCount As Long
' This variable holds the initials for a person being processed.
Dim sInitials As String
' Determine how many rows in the Master Roster worksheet contain names.
' Look in the column for initials. The header cell in that column is
' named "Header_Initials".
With [MasterRoster].Range("Header_Initials").Offset(1)
Do While .Cells(iOccupiedRows).Offset(1) <> ""
iOccupiedRows = iOccupiedRows + 1
Loop
End With
' If there are rows with data in them in the Master Roster then process the names.
If iOccupiedRows > 0 _
Then
' Delete all copies of the Template worksheet. Calls sub named DeleteExistingSheets.
Call DeleteExistingSheets
iExistingSheetsCount = ThisWorkbook.Worksheets.Count
' Iterate/loop through all rows containing data needed to populate a copy of
' the template worksheet.
For iRow = 1 To iOccupiedRows
' Get initials for the row/copy of template being processed.
sInitials = [MasterRoster].Range("Header_Initials").Offset(iRow).Value
' Make copy of the template sheet. Place it after the most recently
' added copy of the template worksheet. USed code name to refer to the
' template worksheet. It is now the ActiveSheet.
[Template].Copy After:=Sheets(iExistingSheetsCount - 1)
' Keep track of how many copies of the template worksheet have been made.
iExistingSheetsCount = iExistingSheetsCount + 1
' Put data into the newly created copy of the template sheet. It is the ActiveSheet.
With ActiveSheet
' Name the newly created copy of the template sheet based on initials.
.Name = sInitials
' Copy data for the current row into the newly created copy of the template worksheet.
.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
