I am trying to create a new worksheet, by copying the 'Template', if one does not exist.
The names of the sheets are based on Column A (list starting from A5 of the 'Master'). The list in 'Master' will be updated daily.
I check the list for new names by looping through the existing Sheets. If a cell in Column A (Sheet 'Master') already has a worksheet with the name, then do nothing and go to the next cell. If a name in the list is not among the sheetnames of the Workbook, a worksheet would be added (a copy of the 'Template') and named after the cell value.
I am able to create the new worksheets but for every existing worksheet, the macro creates additional worksheets ('template(2)', 'template(3)', 'template(4)', and so on).
What should I do to eliminate those new sheets of 'template(#)'?
Here is my code:
Sub AutoAddSheet()
Dim MyCell As Range, MyRange As Range
Set MyRange = Sheets("Master").Range("A5")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
For Each MyCell In MyRange
On Error Resume Next
Sheets("Template").Copy After:=Sheets(Sheets.Count)
With Sheets(Sheets.Count)
.Name = MyCell.Value
.Cells(2, 1) = MyCell.Value
End With
On Error GoTo 0
MyCell.Hyperlinks.Add Anchor:=MyCell, Address:="", SubAddress:="'" & MyCell.Value & "'!A1"
Next MyCell
End Sub