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

 

3 thoughts 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
  2. Meloron

    Dear Sir,

    Thank you verymuch for your videos that have helped me learn excel vba to a point where i am ready to expand. I am writing for two other reasons :

    1. I am tring to correct an excel vba code that will allows the user to see attachments in outlook that come from a particular user on a daily basis. Once the person clicks on the file, the file is copied and pasted in an excel sheet. Please do you think you can help me with that?

    Also i will like to know if you design rapid courses for excelbeginners like me where i can learn specific topics?

    Thank you very much.

    Reply

Leave a Reply

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