How to copy entire data using row by row and paste to another sheet

Question

I'm attempting to transfer my data from one worksheet to another. I want to duplicate the first row on the target sheet, paste it there, then duplicate the second row and paste it there. Actually, I want to duplicate the data in the data sheet row by row, in a loop, till the last row is reached. When the macro reaches the last row and there is no data on it, a pop-up finish message appears.

I've tried the following code, but it doesn't meet my needs. Any advice and assistance would be greatly appreciated.

Sub InsertData()

Dim wsCopy As Worksheet, wsDest As Worksheet
Dim lCopyLastRow As Long, lDestLastRow As Long

'Set variables for copy and destination sheets
Set wsCopy = Workbooks("Warranty Template.xlsm").Worksheets("PivotTable")
Set wsDest = Workbooks("QA Matrix Template.xlsm").Worksheets("Plant Sheet")

'1. Find last used row in the copy range based on data in column A
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, 1).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, 4).End(xlUp).Offset(1,0).Row

'3. Copy & Paste Data
wsCopy.Range("A5:A" & lCopyLastRow).Copy _
wsDest.Range("D" & lDestLastRow)

End Sub

Answer 1

Try this:

Sub InsertData()

    Dim wsCopy As Worksheet, wsDest As Worksheet
    Dim lCopyLastRow As Long, lDestLastRow As Long, row As Long

    'Set variables for copy and destination sheets
    Set wsCopy = Workbooks("Warranty Template.xlsm").Worksheets("PivotTable")
    Set wsDest = Workbooks("QA Matrix Template.xlsm").Worksheets("Plant Sheet")

    '1. Find last used row in the copy range based on data in column A
    lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, 1).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,4).End(xlUp).Offset(1,0).Row

    '3. Copy & Paste Data
    For row = 5 To lCopyLastRow ' Cycle form 5th row (as well as you used range from A5) to last filled row (in A column)
        ' First: by copying data (not effective way, your solution is better)
        wsCopy.Range("A" & row).Copy wsDest.Range("D" & (lDestLastRow + (row - 5)))
        
        ' Second: by filling the data
        ' Like this: wsDest.Range("D" & (lDestLastRow + (row - 5))) = wsCopy.Range("A" & row)
    Next row

End Sub