How to Copy Data from One Workbook to Another & Transpose the Data Using VBA in Microsoft Excel 2010

In this article, you will learn how to copy data from one workbook to another & transpose the data using VBA code.
 
Let us understand with an example:
 
We have 2 workbooks- Book1 & Book2

Book2 contains the data entered in Book1. Refer below snapshot of Book1
 
img1
 
The data entered in Book1 needs to be copied after transposing the data as we click on Command Button (Submit) in Book2. Refer below snapshot of Book2
 
img2
 
As we enter the data in Book1; the data will get copied to Book2 with a single click on Command Button.
 
Click on Developer tab

From Code group, select Visual Basic
 
img3
 
Click on Command Button

This will open Command Button1_Click Object

Enter the following code in the CommandButton1 Object

Private Sub CommandButton1_Click()

Dim Product As String

Dim Price As Single

Dim Book2 As Workbook

 

Worksheets(“sheet1″).Select

Product = Range(“B1″)

Price = Range(“B2″)

 

Set Book2 = Workbooks.Open(“F:\USER Account\Desktop\Excel\Book2.xlsx”)

Worksheets(“sheet1″).Range(“A1″).Select

RowCount = Worksheets(“sheet1″).Range(“A1″).CurrentRegion.Rows.Count

    With Worksheets(“sheet1″).Range(“A1″)

        .Offset(RowCount, 0) = Product

        .Offset(RowCount, 1) = Price

    End With

Book2.Save

End Sub

 
img4
 
After clicking on Submit button on Book1, the data will get transfer to Book2.

If we enter other Products information in Book1 & as we click on the Submit button, the Book2 will get open & the data will get copied & then saved.

Snapshot of Book1
 
img5
 
Result in Book2
 
img6
 

In this way, we can easily copy the data from one Excel workbook to another.
 
 

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