Copy data from lastrow to first empty row on new sheet created

[jo_hivera]

Good morning all!

I've been trying to used multiple posts for reference, but i'm stuck on the last piece of code to make this sheet work.
When i create a new sheet, based on the submission of data through a userform, it should also copy the data to the new sheet created (to automatically start the employees sheet).
For context:
Name new worksheet with range from lastrow on list

However, my problem now is a line of code that is assuming a the first empty row.
Instead of finding the next available row it's finding the next available row after a second header on the sheet.
The data comes from the main sheet's last row and should be pasted on the range A2:F2 of each new created sheet.
Meaning, i.e. the information from A3:F3 ("Checklist" sheet) should have been copied to A2:F2 on the "steve" sheet.

I'm attaching pictures of the sheet and code applied.

This is my code so far:

Private Sub SUBMIT1_Click()
Dim LR As Long
Dim ws As Worksheet
Dim strName As String
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long
   
    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

'Set variables for copy and destination sheets
 
  Set wsDest = Worksheets(Worksheets.Count)
   
  '1. Find last used row in the copy range based on data in column A
  lCopyLastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
   
  '2. Find first blank row in the destination range based on data in column A
  'Offset property moves down 1 row
  lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row

  '3. Copy & Paste Data
  ws.Range("A2:F" & lCopyLastRow).Copy _
    wsDest.Range("A" & lDestLastRow)


    Worksheets("Checklist").Activate

End Sub

All suggestions are welcome!
Thank you in advance!

 

[JLGWhiz]

Try changing this

ws.Range("A2:F" & lCopyLastRow).Copy _
    wsDest.Range("A" & lDestLastRow)

To this

ws.Range("A2:F" & lCopyLastRow).Copy _
    wsDest.Range("A2")

 

[jo_hivera]

Try changing this

ws.Range("A2:F" & lCopyLastRow).Copy _
    wsDest.Range("A" & lDestLastRow)

To this

ws.Range("A2:F" & lCopyLastRow).Copy _
    wsDest.Range("A2")

I tried it, but it copies all of the data on the "checklist" sheet.

 

[JLGWhiz]

Yes, I read the code wrong. Leave the other as is was originally and This is the change you needed to make

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

 

[jo_hivera]

Yes, I read the code wrong. Leave the other as is was originally and This is the change you needed to make

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

It didn't work as well, it copied all of the data below the second header (employee's sheet), i need to copy the last row below the second header (A2:F2) when a new sheet is created.

 

[JLGWhiz]

Apparently, I am not understanding which sheet is which.

Sheets("Checklist").Cells(Rows.Count, 1).End(xlUp).Resize(, 6).Copy wsDest.Range("A2")

should put the data on the correct row. I was just trying to stay with your original code structure.

 

[jo_hivera]

Apparently, I am not understanding which sheet is which.

Sheets("Checklist").Cells(Rows.Count, 1).End(xlUp).Resize(, 6).Copy wsDest.Range("A2")

should put the data on the correct row. I was just trying to stay with your original code structure.

Sorry it took me so long to reply (i've been out of office).
I wouldn't mind if you changed the initial code structure, I am here to learn after all!
But it works and this little change actually helps me a lot with other projects! Thank you so much!

 

[JLGWhiz]

Sorry it took me so long to reply (i've been out of office).
I wouldn't mind if you changed the initial code structure, I am here to learn after all!
But it works and this little change actually helps me a lot with other projects! Thank you so much!

You're welcome,
regards, JLG