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. However, 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 7 suitable and tricky ways to transfer data from one sheet to another in Excel using macros. Hence, read through and save time.

Download Practice Workbook

You can download the workbook used for the demonstration from the download link below.

7 Effective Ways to Transfer Data from One Sheet to Another in Excel Using Macros

In this tutorial, I will show you how to transfer data from one sheet to another using macros in Excel. Here, I have used 7 different methods to make you understand the scenario properly. For the purpose of demonstration, I have used the following sample dataset.

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

In the beginning, we will show you to transfer data from one sheet to another using Range. Copy method using macros. However, you must go through the steps below to learn the process.

📌 Steps:

• Firstly, hold the Alt + F11 keys in Excel, which opens the Microsoft Visual Basic Applications window.
• Secondly, click the Insert button and select Module from the menu to create a module.

• Thirdly, a new window will open and write the following VBA macro in the Module.

Sub CopyRangeToAnotherSheet()
 Sheets("Dataset").Range("B5:C10").Copy Destination:=Sheets("Range.Copy").Range("B5:C10")
End Sub

• Finally, press the F5 key to run the VBA code.

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

2. Apply VBA Macro to Move Data from One Active Worksheet to Another in Excel

In the previous section, we didn’t require the worksheet to activate. But in this section, we will learn how to copy and paste data in an active worksheet.

📌 Steps:

• Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• In the code window, copy the following code and paste it.

Sub CopyPasteToAnotherActiveSheet()
    'Copy data from source sheet
    Sheets("Dataset").Range("B5:C10").Copy
    'Activate destination sheet
    Sheets("Active Sheet to Another").Activate
    'Select destination range
    Range("B5:C10").Select
    'Paste in the destination
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

• Next, run the code shown above and see the result in the following image.

Read More: How to Get Data from Another Sheet Based on Cell Value in Excel

3. Paste Copied Data from One Worksheet to Another with PasteSpecial in Macro

In addition, you can copy data from one worksheet and paste those in various ways with Excel’s PasteSpecial method with VBA. However, PasteSpecial is a feature that gives you more control over deciding how the content is displayed. With Paste Special, you can copy not only data but also the source column’s width into the target columns. In addition, you can even use Paste Special to perform calculations like addition, subtraction, multiplication, and division. Hence, the steps to do that are given below.

📌 Steps:

• First, insert a Module in the code window.
• Second, copy the following code and paste it into the code window.

Sub CopyPasteSpecial()
    Worksheets("Dataset").Range("B5:C10").Copy
    Worksheets("PasteSpecial").Range("B5:C10").PasteSpecial
End Sub

• At last, run the macro in order to get your desired result.

4. Implement Macro to Duplicate Data from One Sheet to Another with USEDRANGE Property

This time, we will learn the VBA code on how to copy and paste data from one worksheet to another with the UsedRange attribute in Excel. However, it provides a Range object in response that specifies the worksheet’s active range. Read-only. Hence, read through the following steps to complete the task properly.

📌 Steps:

• Initially, create a Module in the code window.
• Afterward, copy the code below and paste it into the code window.

Sub CopyWithUsedRange()
'Set variables
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
'Set variables for source and destination sheets
Set wsSource = Worksheets("Dataset")
Set wsTarget = Worksheets("USEDRANGE")
'Copy data from the source and Paste in the destination
Call wsSource.UsedRange.Copy(wsTarget.Cells(2, 2))
End Sub

• Similarly, you will receive the final output if you run the VBA.

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)

5. Transfer a Single Cell from One Worksheet to Another in Excel Through Macros

In the above sections, you have learned how to copy and paste a range of data from one worksheet to another. Now, you will see how to copy and paste when you have a single piece of data in your Excel spreadsheet. However, the Range sheet may consist of one or more values, but we will pick only one cell, C5. Hence, follow the steps mentioned below.

📌 Steps:

• As shown above, open Visual Basic Editor from the Developer tab and insert a Module in the code window.
• In the code window, copy the following code and paste it.

Sub CopyPasteSingleRangeToAnotherSheet()
Worksheets("Dataset").Range("C5").Copy _
Worksheets("Transferring Single Cell").Range("C5")
End Sub

• In the end, hit the Run button to run this piece of code and notice the following image.

6. Apply VBA Macros to Move Specific Data from One Sheet to Another

Furthermore, we can filter the source dataset and copy and paste only the specific data into another worksheet in Excel. So, follow this article to learn how to do that step by step with VBA.

📌 Steps:

• In the beginning, hold the Alt + F11 keys in Excel, which opens the VBA window.
• Then, click the Insert button and select Module from the menu to create a module.
• Next, write the following macro into the module.

Sub CopyPasteSelectedData()
'Set variables
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
'Set variables for source and destination sheets
Set wsSource = Worksheets("Dataset")
Set wsTarget = Worksheets("Transferring Specific Data")
'Copy data from the source
wsSource.Range("B5:C7").Copy
'Paste data in the destination
Call wsTarget.Range("B5:B7").PasteSpecial(Paste:=xlPasteValues)
End Sub

• Lastly, you will get a specific range of data if you run the code.

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

7. Transfer Data from One Sheet to Another in Different Workbooks

Last but not least, we will copy the data from the Dataset sheet from the Source Workbook and paste it into the Sheet1 worksheet in the Destination Workbook. But now, the workbook is open and saved. Hence, read the following part to learn more.

📌 Steps:

• First, open Visual Basic Editor from the Developer tab and insert a Module in the code window.
• Second, copy the following code and paste it into the code window.

Sub CopyOneFromAnotherSavedWorkbook()
    Workbooks("Transfer Data from One Sheet to another Using Macros.xlsm") _
    .Worksheets("Transfer to Different Workbooks").Range("B2:C10").Copy _
    Workbooks("Destination Workbook.xlsx").Worksheets("Sheet1").Range("B2")
End Sub

• After that, run the code, and it will transfer the data to a new open and saved workbook.

Things to Remember

• First, you do not need to select or activate the workbooks, worksheets, or even ranges. This is because the code already specifies those details.
• Then, you must reference the correct file extension in the Workbooks property.
• Next, the process is the same for inserting a Module in the VBA window for every method.
• After that, make sure the works are micro-enabled, and the file name is saved as .xlsm; otherwise, the code will not work for those files.

Conclusion

These are all the steps you can follow to transfer data from one sheet to another using macros in Excel. Overall, in terms of working with time, we need this for various purposes. I have shown multiple methods with their respective examples, but there can be many other iterations depending on numerous situations. Hopefully, you can now easily create the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.

For more information like this, visit Exceldemy.com.

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)