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

Get FREE Advanced Excel Exercises with Solutions!

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.


Watch Video – Transfer Data from One Sheet to Another Using Macros in Excel


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.

How to Transfer Data from One Sheet to Another in Excel Using Macros 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

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

  • 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

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

  • 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

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

  • 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

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

  • 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


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

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

  • 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

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

  • 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

Transfer Data from One Sheet to Another in Different Workbooks

  • 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.

Download Practice Workbook

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

 


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdullah Al Murad
Md. Abdullah Al Murad

Md. Abdullah Al Murad is a computer engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in C, C++, Python, JavaScript, HTML, SQL, MySQL, PostgreSQL, Microsoft Office, and VBA and goes beyond the basics. He holds a B.Sc. in Computer Science and Engineering from American International University-Bangladesh. He has transitioned into the role of a content developer. Beyond his work, find... Read Full Bio

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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo