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
.Offset(RowCount, 0) = Product
.Offset(RowCount, 1) = Price
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.
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.