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

jo_hivera

New Member
Joined
May 29, 2020
Messages
28
Office Version
  1. 2019
Platform
  1. Windows
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.
Q1_01_01.PNG

Q1_05_01.PNG


This is my code so far:
VBA Code:
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!
 

Attachments

  • Q1_02.PNG
    Q1_02.PNG
    17.4 KB · Views: 5
  • Q1_05.PNG
    Q1_05.PNG
    210 KB · Views: 5

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,519
Office Version
  1. 2013
Platform
  1. Windows
Try changing this
VBA Code:
ws.Range("A2:F" & lCopyLastRow).Copy _
    wsDest.Range("A" & lDestLastRow)
To this
VBA Code:
ws.Range("A2:F" & lCopyLastRow).Copy _
    wsDest.Range("A2")
 

jo_hivera

New Member
Joined
May 29, 2020
Messages
28
Office Version
  1. 2019
Platform
  1. Windows
Try changing this
VBA Code:
ws.Range("A2:F" & lCopyLastRow).Copy _
    wsDest.Range("A" & lDestLastRow)
To this
VBA Code:
ws.Range("A2:F" & lCopyLastRow).Copy _
    wsDest.Range("A2")

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

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,519
Office Version
  1. 2013
Platform
  1. Windows
Yes, I read the code wrong. Leave the other as is was originally and This is the change you needed to make
VBA Code:
 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

New Member
Joined
May 29, 2020
Messages
28
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Yes, I read the code wrong. Leave the other as is was originally and This is the change you needed to make
VBA Code:
 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

Well-known Member
Joined
Feb 7, 2012
Messages
12,519
Office Version
  1. 2013
Platform
  1. Windows
Apparently, I am not understanding which sheet is which.
VBA Code:
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

New Member
Joined
May 29, 2020
Messages
28
Office Version
  1. 2019
Platform
  1. Windows
Apparently, I am not understanding which sheet is which.
VBA Code:
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,519
Office Version
  1. 2013
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,013
Messages
5,545,483
Members
410,685
Latest member
chandraganji
Top