Sub NewSheets()
Dim agent As String
Dim lastone As String
Dim i As Integer
Dim max As Integer
Dim ws As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
max = InputBox("How Many Rows down the list do you wish to use?", "Enter a number to create that number of sheets")
For i = 1 To max
'for the first of the new sheets, the previous sheet is called template, by definition
If i = 1 Then
lastone = "Template"
'otherwise, the last previous sheet name will be one higher in the list than the current
Else
lastone = Worksheets("Lists").Range("A1").Offset(i - 1, 0).Value
End If
'get the agent name
agent = Worksheets("Lists").Range("A1").Offset(i, 0).Value
'check whether a sheet with that name already exists
On Error Resume Next
Set ws = Sheets(agent)
'if none with that name, create a new sheet
If ws Is Nothing Then
ActiveWorkbook.Sheets("Template").Copy after:=ActiveWorkbook.Sheets(lastone)
ActiveSheet.Name = agent
'if the sheet already exists, ask whether the user wants to replace it
Else
overW = MsgBox("This sheet already exists - Overwrite?", vbYesNoCancel, agent)
If overW = vbYes Then
Sheets(agent).Delete
ActiveWorkbook.Sheets("Template").Copy after:=ActiveWorkbook.Sheets(lastone)
ActiveSheet.Name = agent
ElseIf overW = vbNo Then
GoTo Skip
ElseIf overW = vbCancel Then
Exit Sub
End If
End If