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
 
img1
 
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
 
img2
 
Click on Developer tab

From Code group, select Visual Basic
 
img3
 
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

Sheet1.Range(“B2″).Copy

Sheet2.Range(“B” &i).PasteSpecialxlValues

Sheet1.Range(“B3″).Copy

Sheet2.Range(“C” &i).PasteSpecialxlValues

Application.CutCopyMode = False

End Sub
 
img4
 
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
 
img5
 
To see the result in Daily Activity, refer below snapshot
 
img6
 
In this way, we can copy the data from one sheet to another.
 
 

Users are saying about us...

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube