vba macro to batch process adding worksheets

Hi Experts,


In need of your help as usual. I have a manually triggered code to manually create a new worksheet by manually entering the sheet name and the code checks for duplicates, copies master template sheet and renames it to newly entered name.

Now, I need a batch processing vba code to look at my names list in a range e.g A:A take each value, trigger my macro and enter the referenced name into it then letting the macro create a sheet and name it and then move on to the next name on the list.

This is my current macro code to add a new sheet:

Sub addnewsheetbutton()
' addnewsheetbutton Macro
'First, jump through the validation hoops
Dim AddSheetQuestion As Variant

'Define the application input box question
showAddSheetQuestion:
Dim QuestionText As String
Dim ValidName As Boolean


QuestionText = "Please enter the name of the sheet you want to add," & vbCrLf & "or click the Cancel button to cancel the addition:"


Do
AddSheetQuestion = Application.InputBox(QuestionText, _
"What sheet do you want to add?")


If AddSheetQuestion = False Then
MsgBox "You clicked the Cancel button." & vbCrLf & "No new sheet will be added.", 64, "Cancel was clicked."
Exit Sub
End If


ValidName = InvalidName(AddSheetQuestion)


QuestionText = "You entered an Invalid sheet name. Please try again."


Loop Until (ValidName = False)


'See if a worksheet exists that is named as the new name being attempted to add.
'We want this code to error, because if it does, it will mean no such sheet exists
'so we can complete this macro.

On Error Resume Next
Worksheets(UCase(AddSheetQuestion)).Activate
If Err.Number <> 9 Then
Err.Clear
GoTo ErrorHandler1
End If


'Here's the actual sheet addition code
With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
End With

 
'Make the Template sheet visible, and copy it
With Worksheets("template")
.Visible = xlSheetVisible
.Activate
End With

'Copy template sheet
Sheets("template").Copy After:=Worksheets(Worksheets.Count)
NewPageName = AddSheetQuestion
ActiveWindow.ActiveSheet.Name = NewPageName
Sheets("template").Visible = False

With Application
.GoTo Range("B12"), True
End With

'Hide Template sheet
Worksheets("template").Visible = xlSheetVeryHidden


With Application
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
End With


'Inform the user the macro is completed
MsgBox "The new sheet name ''" & AddSheetQuestion & "'' has been added.", 64, "Sheet addition successful."
Exit Sub


'If a worksheet exists named with the same three-character code
ErrorHandler1:
MsgBox "A worksheet already exists that is named " & AddSheetQuestion & "." & vbCrLf & vbCrLf & _
"Please click OK, verify the name you really" & vbCrLf & _
"want to add, and try again." & vbCrLf & vbCrLf & "Sheet addition cancelled.", 48, "Sorry, that name already taken."
Exit Sub


End Sub
Public Function InvalidName(ByVal SheetName As String) As Boolean
With CreateObject("VBScript.RegExp")
.IgnoreCase = True
.Global = True
.Pattern = "[\[/\?\]\*()]"
InvalidName = .test(SheetName)
End With
End Function

Thanks in advance...

Maybe:

Sub SKooLZz()
Dim i As Long
Dim ws As Worksheet
Set ws = ActiveSheet
With ws
For i = 2 To .Range("A" & Rows.Count).End(3).row
    On Error Resume Next
    Sheets.Add after:=Sheets(Sheets.Count)
    ActiveSheet.name = .Cells(i, "A").Value
Next i
End With
End Sub

Many thanks JOHN H. DAVIS... your code worked a treat.... and I've customised it to my activity.

Just 1 more additional cry for help, how do I amend the code to first check if the sheet with the same name exists and if so skip the addition and move to the next. This is my code which is copying and renaming a master template:

Sub batchmaker()
Dim i As Long
Dim ws As Worksheet
Dim Newpagename As String
Set ws = ActiveSheet
With ws
For i = 2 To .Range("A" & Rows.Count).End(3).Row
    On Error Resume Next
    
'Make the Template sheet visible, and copy it
Worksheets("template").Visible = xlSheetVisible
Worksheets("template").Activate

'Copy template sheet
Sheets("template").Copy After:=Worksheets(Worksheets.Count)
Newpagename = .Cells(i, "A").Value
ActiveWindow.ActiveSheet.Name = Newpagename
Sheets("template").Visible = False

'Hide Template sheet
Worksheets("template").Visible = xlSheetVeryHidden

Next i
End With
End Sub

Oh. I could also do with code to reverse this and batch search and delete sheets. I know I ask for too much but I'm a newbie to vba it may take me 3 hours to work out how to doe it when experts take 3-5 so your help is greatly appreciated.


Thanks in Advance..