Copy Template worksheet From List - adding new Names to List keeping previous sheets intact

[Loco1010]

Hi,
I'm new to this site and VBA. I came across solution that works fine for me up to the point where I add new items to the list and then Macro tries to overwrite sheets that are already there returning error about the name being taken...

what I have now:
I have workbook with sheets "List" and "Template".
In List I have names/products in column D (starting from D2).
This list will grow in time as I'll be adding new items.
I need code to copy sheet Template and give the new ones the names from list.
Some values are being put in new sheets from List cells as well.

code below does all that.

however, when I add anything to the List (column D) macro tries to do it from scratch and returns error - what I need is modification to the code so that whatever is already in the file is intact and code just adds new sheets for names that are currently added - not sure if it makes sense...(
for example in "List" I already have D2 to D4 taken by inputs X, XX, XXX. I run macro and it created three new sheets named X, XX, and XXX which are copies of Template. I add info to X, XX and XXX sheets and save the file. now I'm opening the file again (all worksheets are there) and add ZZZ into D5 in "List" what I need is to leave everything as is just add new copy of "Template" named ZZZ. change what's needed and save. And so on...

current code:

Sub Create_WS()
Dim MyCell As Range, MyRange As Range
Set MyRange = Sheets("List").Range("d2")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
For Each MyCell In MyRange
Sheets("Template").copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = MyCell.Value 'this is where code gives error when re-run
Sheets(MyCell.Value).Range("C20").Value = MyCell.Offset(0, -1).Value
Sheets(MyCell.Value).Range("c9").Value = MyCell.Offset(0, 1).Value
Next MyCell
End Sub


I would appreciate your help in getting it sorted.
thanks

 

[RCBricker]

Hi,
I'm new to this site and VBA. I came across solution that works fine for me up to the point where I add new items to the list and then Macro tries to overwrite sheets that are already there returning error about the name being taken...

what I have now:
I have workbook with sheets "List" and "Template".
In List I have names/products in column D (starting from D2).
This list will grow in time as I'll be adding new items.
I need code to copy sheet Template and give the new ones the names from list.
Some values are being put in new sheets from List cells as well.

code below does all that.

however, when I add anything to the List (column D) macro tries to do it from scratch and returns error - what I need is modification to the code so that whatever is already in the file is intact and code just adds new sheets for names that are currently added - not sure if it makes sense...(
for example in "List" I already have D2 to D4 taken by inputs X, XX, XXX. I run macro and it created three new sheets named X, XX, and XXX which are copies of Template. I add info to X, XX and XXX sheets and save the file. now I'm opening the file again (all worksheets are there) and add ZZZ into D5 in "List" what I need is to leave everything as is just add new copy of "Template" named ZZZ. change what's needed and save. And so on...

current code:

Sub Create_WS()
Dim MyCell As Range, MyRange As Range
Set MyRange = Sheets("List").Range("d2")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
For Each MyCell In MyRange
Sheets("Template").copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = MyCell.Value 'this is where code gives error when re-run
Sheets(MyCell.Value).Range("C20").Value = MyCell.Offset(0, -1).Value
Sheets(MyCell.Value).Range("c9").Value = MyCell.Offset(0, 1).Value
Next MyCell
End Sub


I would appreciate your help in getting it sorted.
thanks

try this.

Sub Create_WS()
Dim MyCell As Range, MyRange As Range
Dim lngROW As Long
Dim ws As Worksheet, wsTEMP As Worksheet, wsNEW As Worksheet
Dim strSHTNAME As String
Dim boolNAME As Boolean

'*******************************************************************************
'       Sets two worksheet variables to the two primary worksheets.
'*******************************************************************************
    Set ws = Sheets("List")
    Set wsTEMP = Sheets("Template")
    
'*******************************************************************************
'       WITH block which affects the worksheet in question
'       long variable that finds the last used row in column D
'       range variable set to all used rows in column D
'*******************************************************************************
    With ws
        lngROW = ws.Range("D" & .Rows.Count).End(xlUp).Row
        Set MyRange = ws.Range(ws.cells(2, 4), ws.cells(lngROW, 4))
    End With
    
'*******************************************************************************
'       FOR EACH loop which iterates through the cells in myrange.
'       String variable set to the current cell's value
'       Boolean variable which calls a function and passes the string varaible
'*******************************************************************************
    For Each MyCell In MyRange
        strSHTNAME = MyCell.Value
        boolNAME = SheetExists(strSHTNAME)
        
'*******************************************************************************
'       IF statement that checks that the value of the boolean variable is not
'           true
'       if false then
'           Copies the template to the end of the worksheet list
'           sets a worksheet variable to the new sheet
'           changes the name of the worksheet to the cell value
'           populates two cells in the new sheet from cell values near the
'               current cell.
'*******************************************************************************
        If Not boolNAME = True Then
            wsTEMP.Copy After:=Sheets(Sheets.Count)
            Set wsNEW = Sheets(Sheets.Count)
            wsNEW.Name = strSHTNAME 'this is where code gives error when re-run
            wsNEW.Range("C20").Value = MyCell.Offset(0, -1).Value
            wsNEW.Range("c9").Value = MyCell.Offset(0, 1).Value
        End If
    Next MyCell
End Sub
 Function SheetExists(shtName As String, Optional wb As Workbook) As Boolean
    Dim sht As Worksheet

'*******************************************************************************
'       IF statement that checks to see if a workbook variable was passed,
'           if not then set to the current workbook
'*******************************************************************************
    If wb Is Nothing Then Set wb = ThisWorkbook
    
'*******************************************************************************
'       On Error to resume next line in code if an error fires
'       set worksheet variable to the worksheet named as the passed variable
'       returns error handling to zero
'       sets boolean to true if worksheet is not nothing
'*******************************************************************************
On Error Resume Next
     Set sht = wb.Sheets(shtName)
On Error GoTo 0
     SheetExists = Not sht Is Nothing
 End Function

This added a function to check is a worksheet already exists... if so do nothing.

I added a couple variable and functionality to your code.

 

[Loco1010]

thanks RCBricker, neat solution, works well.