Name new Worksheet with range from LastRow on list

[jo_hivera]

Good Afternoon,

I'm currently having trouble with my code to name a new sheet (when created) with the value from column A, using the last row used as guidance.
The userform adds the data to the list and it should copy a worksheet named "Template" and rename itself to the last info added on column A on the "Checklist" sheet.

I've been teaking it and this i what i have so far:

Private Sub SUBMIT1_Click()
Dim LR As Long
Dim ws As Worksheet
Set ws = Worksheets("Checklist")
'Determine LR

  LR = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
  
'Transfer information

Cells(LR, 1).Value = NAME1.Value
Cells(LR, 2).Value = CODE1.Value
Cells(LR, 3).Value = ID1.Value
Cells(LR, 4).Value = LEVEL1.Value
Cells(LR, 5).Value = DEPT1.Value
Cells(LR, 6).Value = DATE1.Value

NAME1.Value = ""
CODE1.Value = ""
ID1.Value = ""
LEVEL1.Value = ""
DEPT1.Value = ""
 
'When you submit data, it creates new employee sheet,
'using the "Template" Worksheet and naming it after them
  Worksheets("Template").Copy After:=Worksheets(Worksheets.Count)
  Worksheets(Worksheets.Count).NAME = ws.NAME(ws.Cells(Rows.Count, 1)).End(xlUp).Row + 1

  Worksheets("Checklist").Activate

End Sub

The problem lies mainly in this section:

'When you submit data, it creates new employee sheet,
'using the "Template" Worksheet and naming it after them
  Worksheets("Template").Copy After:=Worksheets(Worksheets.Count)
  Worksheets(Worksheets.Count).NAME = ws.NAME(ws.Cells(Rows.Count, 1)).End(xlUp).Row + 1

I'm adding screenshots of the sheets, the code i started with and the code i currently have for context.
All suggestions are welcome!
Thank you!

 

[Norie]

What are you trying to do with ws.NAME here?

Worksheets(Worksheets.Count).NAME = ws.NAME(ws.Cells(Rows.Count, 1)).End(xlUp).Row + 1

Shouldn't it be like this?

Worksheets(Worksheets.Count).Name = ws.Cells(Rows.Count, 1)).End(xlUp).Row

 

[jo_hivera]

What are you trying to do with ws.NAME here?

Worksheets(Worksheets.Count).NAME = ws.NAME(ws.Cells(Rows.Count, 1)).End(xlUp).Row + 1

Shouldn't it be like this?

Worksheets(Worksheets.Count).Name = ws.Cells(Rows.Count, 1)).End(xlUp).Row

Hey Norie!
Thanks in advance for the help!
I didn't know if I needed to reference .NAME to name the sheet.
When i submit the userform data it should make a copy of the "Template" sheet and name itself after the Name on the employee list.
I tried your code, but the sheet named itself after the row the last name was added, for some reason.
It should look like the picture, in attachment.

 

[Norie]

I'm a little confused, what do you want to name the worksheet?

Will it be with the value in the textbox NAME1?

 

[jo_hivera]

I'm a little confused, what do you want to name the worksheet?

Will it be with the value in the textbox NAME1?

Yes. If possible, of course...

 

[Norie]

Try this.

Private Sub SUBMIT1_Click()
Dim LR As Long
Dim ws As Worksheet
Dim strName As String

    Set ws = Worksheets("Checklist")
    'Determine LR

    LR = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1

    'Transfer information
    With ws
        .Cells(LR, 1).Value = NAME1.Value
        .Cells(LR, 2).Value = CODE1.Value
        .Cells(LR, 3).Value = ID1.Value
        .Cells(LR, 4).Value = LEVEL1.Value
        .Cells(LR, 5).Value = DEPT1.Value
        .Cells(LR, 6).Value = DATE1.Value
    End With
    
    strName = NAME1.Value
    
    NAME1.Value = ""
    CODE1.Value = ""
    ID1.Value = ""
    LEVEL1.Value = ""
    DEPT1.Value = ""

    'When you submit data, it creates new employee sheet,
    'using the "Template" Worksheet and naming it after them
    Worksheets("Template").Copy After:=Worksheets(Worksheets.Count)
    Worksheets(Worksheets.Count).Name = strName

    Worksheets("Checklist").Activate

End Sub

 

[jo_hivera]

Try this.

Private Sub SUBMIT1_Click()
Dim LR As Long
Dim ws As Worksheet
Dim strName As String

    Set ws = Worksheets("Checklist")
    'Determine LR

    LR = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1

    'Transfer information
    With ws
        .Cells(LR, 1).Value = NAME1.Value
        .Cells(LR, 2).Value = CODE1.Value
        .Cells(LR, 3).Value = ID1.Value
        .Cells(LR, 4).Value = LEVEL1.Value
        .Cells(LR, 5).Value = DEPT1.Value
        .Cells(LR, 6).Value = DATE1.Value
    End With
   
    strName = NAME1.Value
   
    NAME1.Value = ""
    CODE1.Value = ""
    ID1.Value = ""
    LEVEL1.Value = ""
    DEPT1.Value = ""

    'When you submit data, it creates new employee sheet,
    'using the "Template" Worksheet and naming it after them
    Worksheets("Template").Copy After:=Worksheets(Worksheets.Count)
    Worksheets(Worksheets.Count).Name = strName

    Worksheets("Checklist").Activate

End Sub

Works wonders! Thank you so much!!