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

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

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

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

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

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

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