Copy paste data repeatedly from sheet1 to sheet2

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:

Copy Paste Data repeatedly from sheet1 to sheet2

Copy Paste Data repeatedly from sheet1 to sheet2

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

 

One thought on “Copy paste data repeatedly from sheet1 to sheet2

  1. Zeeshan

    I am looking to Link my Sales which is sheet1 to my aging report which is sheet5.
    The Sales sheet contain all the detail of daily invoices like date, customer name, delivery order, invoice number, amount. And when we are receiving payment the next column we used as received amount to mark the date & blank cell shows that the amount is still unpaid.
    Based on blank cell of Received amount column, i want to make a Aging report in the same workbook.
    If you can help me out. Coz m new to VBA

    Best Regards,
    Zeeshan

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *