How to copy paste data repeatedly from sheet1 to sheet2 using VBA. A user wishes to repeatedly enter data into sheet1 and automatically transfer to sheet2 as shown in the image below:

Watch the video below and then study the VBA code:

 

Watch this video on YouTube.

The complete VBA code to copy paste automatically from sheet1 to sheet2:

Sub copyFirstdata()
Dim lastrow As Long
lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
Sheet1.Range(Cells(1, 1), Cells(lastrow, 2)).Copy Sheet2.Cells(1, 1)
End Sub

Sub copyNextData()
Dim lastrow As Long, lastrow2 As Long
lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
lastrow2 = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row
Sheet1.Range(Cells(1, 1), Cells(lastrow, 2)).Copy Sheet2.Cells(lastrow2 + 1, 1)
Sheet1.Range(“A1”).Select
End Sub

Sub clearDataSheet1()
Dim lastrow As Long
lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
Sheet1.Range(Cells(1, 1), Cells(lastrow, 2)).Clear
End Sub

Further reading:

How to find the next blank row for data entry using VBA

How to find the last row used with VBA