How to Transfer Data from One Sheet to another in Excel Using Macros

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

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

Read More: How to Import Data into Excel from Another Excel File (2 Ways)

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

Read More: Transfer Data from One Excel Worksheet to Another Automatically with VLOOKUP

Similar Readings

• How to Import Text File with Multiple Delimiters into Excel (3 Methods)
• Excel VBA: Pull Data Automatically from a Website (2 Methods)
• How to Convert Notepad to Excel with Columns (5 Methods)
• Extract Filtered Data in Excel to Another Sheet (4 Methods)
• How to Extract Data from Image into Excel (With Quick Steps)

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

Read More: Extract Data from One Sheet to Another Using VBA in Excel (3 Methods)

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.

Related Articles

• How to Pull Data From Another Sheet Based on Criteria in Excel
• Excel Formula to Get First 3 Characters from a Cell(6 Ways)
• Extract Text After a Character in Excel (6 Ways)
• How to Extract Month from Date in Excel (5 Quick Ways)
• Excel Macro: Extract Data from Multiple Excel Files (4 Methods)
• How to Extract Year from Date in Excel (3 Ways)