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
 

 
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
 

 
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
 

 
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

 

 
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
 

 
Result in Book2
 

 

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