Copy templete into new sheet, with name from cell

Question

I created a workbook that generates new sheets, where the name of the new sheet is taken from a cell in the first sheet.

I wish that these new sheets to be a copy of yet another sheet.

So the sheet "dispostition" is where I write the names in the range "a2:a2000". Eg. 233.

233 becomes a new sheet, which is a copy of the sheet "template".

I'm quite unexperienced in VBA, so the code is something I fund online and modified.

I've tried to change Worksheet.add to Worksheet(template).copy

But this doesn't seem to do the trick.

Sub CreateSheets()
    Dim StartSheet As Worksheet
    Set StartSheet = ActiveSheet
    Dim rng As Range
    Dim cell As Range
    On Error GoTo Errorhandling
    
    If MsgBox("Opret ark baseret på løbenumre?", vbYesNo + vbQuestion) = vbNo Then
    Exit Sub
    End If
    
    Set rng = Range("A2:a2000")
    For Each cell In rng
        If cell <> "" Then
            Worksheets.Add(After:=Worksheets("disposition")).Name = cell
            Sheets("Template").Copy Worksheets(cell).Range("A1")
        End If
    Next cell
    
    Errorhandling:
    StartSheet.Activate
End Sub

Pᴇʜ Pᴇʜ · Accepted Answer · 2020-12-17T13:51:50

Instead of adding a new worksheet, just directly copy it using the Worksheet.Copy method:

If cell <> "" Then
    Worksheets("Template").Copy After:=Worksheets("disposition") 'adds a copy of the template sheet after disposition
    Worksheets(Worksheets("disposition").Index + 1).Name = cell.value 'renames the new added template copy (which is +1 after disposition sheet)
End If

Copy templete into new sheet, with name from cell

1 Answers