Copy Data from One Sheet to Another Using Worksheet Function in Microsoft Excel 2010

In this article, you will learn how to copy data from one sheet to another using worksheet function.


Let us understand with an example:


We have 2 worksheets i.e. Data & Daily Activity

Data sheet will contain the information entered. Refer below snapshot
We need a code that will copy data to Daily Activity sheet; however, while pasting the data, we need to search according to the ID number column & then paste the data according to its ID number.

There is a submit button that will copy the data to Daily Activity sheet as follows
Click on Developer tab

From Code group, select Visual Basic
Click on CommandButton

This will open CommandButton1_Click Object

Enter the following code in the CommandButton1 Object

Private Sub CommandButton1_Click()

LR = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row

i = Application.WorksheetFunction.Match(Sheet1.Range("B1"), Sheet2.Range("A2:A" &LR), 0) + 1


Sheet2.Range("B" &i).PasteSpecialxlValues


Sheet2.Range("C" &i).PasteSpecialxlValues

Application.CutCopyMode = False

End Sub
After clicking on Submit button on Data sheet, the data will get copied to Daily Activity sheet.

As we enter the Sales information in Data sheet & after clicking on Submit button, the data will get transferred to Daily Activity tab.


Snapshot of Data tab
To see the result in Daily Activity, refer below snapshot
In this way, we can copy the data from one sheet to another.


Leave a Reply

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

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.