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

Transfer Data from One Sheet to another

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.

Transfer Data from One Sheet to Another in Excel VBA Using Range. Copy Method

Step 1: Open Visual Basic from the Developer tab

Open Visual Basic from the Developer tab

Step 2: Select Module from Insert 

Select Module from Insert 

Step 3: Now write the following code in the VBA console

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)

Run the code using the Run button (Shortcut F5)

Step 5: Now go to Sheet2. All the Unit Prices will be transferred here

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.

Transfer Specific Data from One Sheet to Another Through VBA in Microsoft Excel

Step 1: Open the VBA console by following the same process Step 1 to Step 2 then write the following code

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

Now go to the worksheet Sheet3 and run the Macro

Step 3: Open the worksheet Sheet4 and see the output

Open the worksheet Sheet4 and see the output

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


Similar Readings


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.

Transfer Data from One Sheet to Another in Excel Using Macros in Different Workbooks

Step 1: Open the VBA console by following the same process Step 1 to Step 2 then write the following code

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

Now run the VBA code

Step 3: All the selected data will be copied to the destination table

Transfer Data from One Sheet to another

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

Md. Abdullah Al Murad

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

2 Comments
  1. 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? ? ?

    • Hello, ARIZONA AMBER! To enable Developer tab, you have to follow this procedure: Go to Excel Options > Customize Ribbon > Customize the Ribbon > Main Tabs > (Check) Developer tab.

Leave a reply

ExcelDemy
Logo