I have some code below to create new sheets from a template and name them accroding to a cell range which is specified in the code, perhaps it is because I am asking it to do too many things at once, but the code is creating one sheet based on the template specified, and then just renaming it to the name range, rather than creating a new sheet for each one.
Any help would be appreciated.
Dim Sh As Worksheet
Dim shName As String
Dim MyCell As Range, MyRange As Range
Set MyRange = Sheets("Sheet1").Range("A1:A201")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
'name of the sheet template
shName = "WHSCT.xltm"
'Insert sheet template
Set Sh = Sheets.Add(Type:=Application.TemplatesPath & shName, _
On Error Resume Next
For Each MyCell In MyRange
Sh.Name = MyCell.Value
If Err.Number > 0 Then
MsgBox "Change the name of Sheet : " & Sh.Name & " manually"
On Error GoTo 0