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 workshets in the workbook.
Dim iExistingSheetsCount As Long
' This variable holds the initials for a person being processed.
Dim sInitials As String
' This variable holds the team number for a person being processed.
Dim sTeamNumber 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
' Get team number for the row/copy of template being processed.
sTeamNumber = [MasterRoster].Range("Header_TeamNumber").Offset(iRow).Value
' Do not try to create a sheet with no initials.
If sInitials <> "" _
Then
' 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 and team number.
.Name = sInitials & "_" & sTeamNumber
' 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
End If
Next iRow
End If
End Sub
