For different purposes, we may need to move or transfer data from one worksheet to another in Excel. Doing it manually or with formulas may lead to complexity and error. In these cases, there are simple ways to do this kind of thing with Excel macros. In this article, I will show how to transfer data from one worksheet to another in Excel using macros.
Download the Practice Workbook
[N.B: Here Report.xlsm is only for method 3]3 Ways to Transfer Data from One Sheet to another in Excel Using Macros
1. Transfer Data from One Sheet to Another in Excel VBA Using Range. Copy Method
Let’s consider, we have a product dataset with their ID, Brand, Model, and Unit Price. We have two different worksheets named Sheet1 and Sheet2. The product information is in Sheet1. Our task is to copy all the prices of the products and paste them into Sheet2 using VBA code.
Step 1: Open Visual Basic from the Developer tab
Step 2: Select Module from InsertÂ
Step 3: Now write the following code in the VBA console
Code:
Sub CopyRangeToAnotherSheet()
Sheets("Sheet1").Range("E3:E15").Copy Destination:=Sheets("Sheet2").Range("B3")
End Sub
Step 4: Run the code using the Run button (Shortcut F5)
Step 5: Now go to Sheet2. All the Unit Prices will be transferred here
2. Transfer Specific Data from One Sheet to Another Through VBA in Microsoft Excel
Now we will transfer data from one worksheet to another through VBA in Excel for the above dataset. Here we will consider an additional column named Delivered or Not. Our goal is to find out the rows where Not is present in Delivered or Not. We will copy those rows and transfer them into a new worksheet.
Step 1: Open the VBA console by following the same process Step 1 to Step 2 then write the following code
Code:
Sub Specific_Data_from_One()
'Declaring variables
Dim i, Lastrow As Long
Lastrow = Sheets("Sheet3").Range("B" & Rows.Count).End(xlUp).Row
Sheets("Sheet4").Range("A2:E13").ClearContents
For i = 2 To Lastrow
If Sheets("Sheet3").Cells(i, "E").Value = "Not" Then
Sheets("Sheet3").Cells(i, "E").EntireRow.Copy Destination:=Sheets("Sheet4").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next i
End Sub
Step 2: Now go to the worksheet Sheet3 and run the Macro
Step 3: Open the worksheet Sheet4 and see the output
3. Transfer Data from One Sheet to Another in Excel Using Macros in Different Workbooks
Let’s say we have two different workbooks. In the first workbook, we have the product information the same as the previous example and the second workbook is named Report.xlsx. Our task is to simply copy the whole data from the source workbook and paste them into a new workbook using VBA code.
Step 1: Open the VBA console by following the same process Step 1 to Step 2 then write the following code
Code:
Sub copyInTwoWorkbooks()
Workbooks("How to Transfer Data from One Sheet to another in Excel Using Macros.xlsm").Worksheets("Sheet5").Range("B4:F15").Copy _
Workbooks("Report.xlsm").Worksheets("Sheet1").Range("B4")
End Sub
Step 2: Now run the VBA code
Step 3: All the selected data will be copied to the destination table
Things to Remember
- We do not need to select or activate the workbooks, worksheets, or even ranges first. This is because the code already specifies those details.
- We must reference the correct file extension in the Workbooks property (see video above for details).
- Make sure the works are micro enabled and the file name is saved as .xlsx otherwise the code will not work for those files.
Conclusion
These are some ways to transfer data from one sheet to another in Excel using macros. I have shown all the methods with their respective examples but there can be many other iterations. I have also discussed the fundamentals of the used functions. If you have any other method of achieving this, then please feel free to share it with us.
I am on Windows 11 and do not have a developer tab on my program. Can one perform this function using other macros or functions? ? ?