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



Product = Range("B1")

Price = Range("B2")


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


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

    With Worksheets("sheet1").Range("A1")

        .Offset(RowCount, 0) = Product

        .Offset(RowCount, 1) = Price

    End With


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.

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.