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

 

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

3 thoughts on “Copy paste data repeatedly from sheet1 to sheet2”

  1. 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

  2. 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.

Leave a Reply to Meloron Cancel reply

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