Macro to Copy and Paste from One Worksheet to Another (15 Methods)

When we are dealing with multiple Excel sheets, sometimes we have to copy data from one spreadsheet to another. Implementing VBA is the most effective, quickest, and safest method to run any operation in Excel. In this article, we will show you how to copy and paste data from one worksheet to another in Excel with the VBA macro.

Download Workbook

You can download the free practice Excel workbook from here.

15 Methods with VBA to Copy and Paste Data from One Worksheet to Another in Excel

In this section, you will learn 15 methods on how you can copy data from one worksheet and paste that into another with VBA in Excel.

Above is the dataset that this article will consider as our example.

1. Embed VBA Macro to Copy and Paste a Range of Data from One Worksheet to Another

The steps to copy and paste a range of data from one worksheet to another with VBA are described below.

Steps:

• In the beginning, press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

• In the pop-up code window, from the menu bar, click Insert -> Module.

• Now, copy the following code and paste it into the code window.

Sub CopyPasteToAnotherSheet()
Worksheets("Dataset").Range("B2:F9").Copy Worksheets("CopyPaste").Range("B2")
End Sub

Your code is now ready to run.

This piece of code will copy the Range from B2 to F9 from the sheet named Dataset and paste those in the B2 Range in the CopyPaste named sheet.

• Then, press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

Look at the following image.

Finally, all the data from the Dataset sheet is now copied in the CopyPaste sheet in our Excel workbook.

Read More: Excel VBA: Copy Range to Another Workbook

2. VBA Macro to Copy and Paste 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("B2:F9").Copy
    'Activate destination sheet
    Sheets("Paste").Activate
    'Select destination range
    Range("B2").Select
    'Paste in the destination
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

Your code is now ready to run.

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

This time, all the data from the Dataset sheet is now copied in the Paste sheet that we activated before copying the data.

Read More: Excel Formula to Copy Text from One Cell to Another Sheet

3. Copy and Paste a Single Cell from One Worksheet to Another in Excel with VBA Macro

In the above sections, you have learnt 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.

Look at the following image, the Range sheet consists of only one value.

We will see how we can copy and paste this one cell into another sheet in Excel with VBA.

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

Sub CopyPasteSingleRangeToAnotherSheet()
Worksheets("Range").Range("B4").Copy Worksheets("CopyRange").Range("B2")
End Sub

Your code is now ready to run.

• Next, Run this piece of code and notice the following image.

That single data “Copy this Cell” in Cell B4 in the Dataset sheet is now copied in the CopyRange sheet in Cell B2.

Read More: Excel VBA to Copy Only Values to Destination (Macro, UDF, and UserForm)

4. Paste Copied Data from One Worksheet to Another with PasteSpecial Method in Excel Macro

You can copy data from one worksheet and paste those in various ways with Excel’s PasteSpecial method with VBA. The steps to do that are given below.

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 CopyPasteSpecial()
    Worksheets("Dataset").Range("B2:F9").Copy
    Worksheets("PasteSpecial").Range("B2").PasteSpecial
End Sub

Your code is now ready to run.

• Next, Run this piece of code.

Look at the above picture. Data from the Dataset sheet is now transferred in the PasteSpecial sheet in Excel.

Read More: VBA Paste Special to Copy Values and Formats in Excel (9 Examples)

5. Macro to Copy and Paste Data Below the Last Cell from One Worksheet to Another in Excel

We already have some data in the Dataset sheet (shown in the introduction section). Now, look at the upcoming part of this section. We now have some new data in another sheet named Last Cell.

What we want to do here is, we will copy specific data (Cells B5 to F9) from the Dataset sheet and paste those in below the last cell of this Last Cell sheet.

Steps:

• Firstly, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Secondly, copy the following code and paste it into the code window.

Sub CopyPasteBelowTheLastCell()
'Set variables
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
Dim iSourceLastRow As Long
Dim iTargetLastRow As Long
'Set variables for source and destination sheets
Set wsSource = Worksheets("Dataset")
Set wsTarget = Worksheets("Last Cell")
'Find last used row in the source sheet based on data in column B
iSourceLastRow = wsSource.Cells(wsSource.Rows.Count, "B").End(xlUp).Row
'Find first blank row in the destination sheet based on data in column B
'Offset property is to move the copied data 1 row down
iTargetLastRow = wsTarget.Cells(wsTarget.Rows.Count, "B").End(xlUp).Offset(1).Row
'Copy data from the source and Paste in the destination
wsSource.Range("B5:F9" & iSourceLastRow).Copy wsTarget.Range("B" & iTargetLastRow)
End Sub

Your code is now ready to run.

• Next, Run this code. Look at the image below.

Here, only the selected data from the Dataset sheet is now copied below the last cell in the Last Cell sheet in Excel.

Read More: Formula to Copy and Paste Values in Excel (5 Examples)

6. VBA Macro to Clear Worksheet First then Copy and Paste to Another Worksheet

What if you have the wrong data in your existing sheet and you want to extract the original data in there.

Look at the following image. We will clear the data from the Clear Range sheet and store here the data from the Dataset sheet with VBA code.

Steps:

• Firstly, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Secondly, copy the following code and paste it into the code window.

Sub ClearAndCopyPasteData()
'Set variables
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
Dim iSourceLastRow As Long
Dim iTargetLastRow As Long
'Set variables for source and destination sheets
Set wsSource = Worksheets("Dataset")
Set wsTarget = Worksheets("Clear Range")
'Find last used row in the source sheet based on data in column B
iSourceLastRow = wsSource.Cells(wsSource.Rows.Count, "B").End(xlUp).Row
'Find first blank row in the destination sheet based on data in column B
'Offset property is to move the copied data 1 row down
iTargetLastRow = wsTarget.Cells(wsTarget.Rows.Count, "B").End(xlUp).Offset(1).Row
'Clear data from the destination range
wsTarget.Range("B5:F9" & iTargetLastRow).ClearContents
'Copy data from the source and Paste in the destination
wsSource.Range("B5:F9" & iSourceLastRow).Copy wsTarget.Range("B5")
End Sub

Your code is now ready to run.

• Next, Run this piece of code. Look at the following image.

The previous data in the Clear Range sheet is now replaced by the data from the Dataset sheet.

Read More: Macro to Copy Data from One Workbook to Another Based on Criteria

7. Macro to Copy and Paste Data from One Worksheet to Another with Range.Copy Function

Now, we will learn the VBA code on how to copy and paste data from one worksheet to another with the Range.Copy function in Excel.

Steps:

• Firstly, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Secondly, copy the following code and paste it into the code window.

Sub CopyWithRangeCopyFunction()
'Set variables
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
'Set variables for source and destination sheets
Set wsSource = Worksheets("Dataset")
Set wsTarget = Worksheets("Copy Range")
'Copy data from the source and Paste in the destination
Call wsSource.Range("B2:F9").Copy(wsTarget.Range("B2"))
End Sub

Your code is now ready to run.

• Next, Run this piece of code and look at the following image.

We have successfully duplicated data from the Dataset sheet in the Copy Range sheet with the Range.Copy function.

Read More: Excel Formula to Copy Cell value to Another Cell

Similar Readings

• Excel VBA to Copy Rows to Another Worksheet Based on Criteria
• Use VBA to Paste Values Only with No Formatting in Excel
• How to Copy and Paste Visible Cells Only in Excel (3 Easy Ways)
• Copy and Paste is Not Working in Excel (9 Reasons & Solutions)
• How to Copy Multiple Rows in Excel Using Macro (4 Examples)

8. Implement Macro Code to Duplicate Data from One Worksheet 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.

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

Your code is now ready to run.

• Next, Run this piece of code.

As we can see in the above picture, we have successfully copied and pasted data from the Dataset sheet in the UsedRange sheet with the USEDRANGE property.

Read More: How to Copy the Same Value in Multiple Cells in Excel (4 Methods)

9. VBA Macro to Copy and Paste Selected Data from One Sheet to Another in Excel

You can copy and paste only some selected data from one worksheet to another with VBA. Steps to do that are shown below.

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 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("Paste Selected")
'Copy data from the source
wsSource.Range("B4:F7").Copy
'Paste data in the destination
Call wsTarget.Range("B2").PasteSpecial(Paste:=xlPasteValues)
End Sub

Your code is now ready to run.

This code will copy only the Range from B4 to F7 from the Dataset sheet and paste those in the B2 Range in the PasteSelected named sheet.

• Next, Run this code.

Finally, only the selected data from the Dataset sheet are successfully copied and pasted in the Paste Selected sheet in Excel workbook.

Read More: How to Apply VBA PasteSpecial and Keep Source Formatting in Excel

10. Macro Code to Duplicate Data from One Worksheet to Another at the First Blank Row

Here, we will see how to copy data from the Dataset sheet and paste those in the first blank cell in another worksheet in Excel with VBA.

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 FirstBlankCell()
Range("B2:F9", Range("B" & Rows.Count).End(xlUp)).Copy Sheet13.Range("A65536").End(xlUp)
End Sub

Your code is now ready to run.

• Next, Run this chunk of code.

See in the above image. Sheet13 was completely blank. As a result, the executed code pasted the copied data from the Dataset sheet in the very first cell in the Sheet13 sheet in Excel.

Read More: Copy and Paste Values to Next Empty Row with Excel VBA (3 Examples)

11. Embed VBA to Copy and Paste the Auto-Filtered Data from One Excel Sheet to Another

We can filter the source dataset and copy and paste only the filtered data in another worksheet in Excel. Follow this article to learn how to do that step by step with VBA.

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.

Option Explicit
Sub AutoFilter()
    Range("B4:B101").AutoFilter 1, "Dean"
    Range("B4:F9").Copy Sheet17.Range("B" & Rows.Count).End(xlUp)(2)
    Range("B4").AutoFilter
End Sub

Your code is now ready to run.

• Next, Run this code. Only the row that has “Dean” in it, will be filtered and copied in another sheet.

Notice in the above image. Only the filtered data “Dean” from the B Column is now copied and pasted in the Sheet15 sheet.

Read More: How to Copy and Paste in Excel Using VBA (7 Methods)

Similar Readings

• How to Autofilter and Copy Visible Rows with Excel VBA
• Copy Unique Values to Another Worksheet in Excel (5 Methods)
• How to Copy Merged and Filtered Cells in Excel (4 Methods)
• Run Time Error 1004: PasteSpecial Method of Range Class Failed
• How to Paste Link and Transpose in Excel (8 Quick Ways)

12. Paste a Row at the Bottom of a Range While Keeping the Copied Formula from the Above Range

When you want to copy a value and keep the formula inside it while pasting it in another row, then with VBA code you can execute the task easily.

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 PasteRowWithFormulaFromAbove()
    Rows(Range("B" & Rows.Count).End(xlUp).Row).Copy
    Rows(Range("B" & Rows.Count).End(xlUp).Row + 1).Insert xlDown
End Sub

Your code is now ready to run.

• Next, Run this code and look at the image below.

The last row is copied exactly as it is in the row next to it.

Read More: How to Copy Data from One Cell to Another in Excel Automatically

13. VBA to Replicate Data from One Sheet to Another Sheet in Another Open but Not Saved Workbook

Notice the name of our example workbook, Source Workbook. We will copy the data from the Dataset sheet from this workbook and paste it into another worksheet in another workbook named Destination Workbook that is open but not saved yet.

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 CopyOneFromAnotherNotSaved()
    Workbooks("Source Workbook").Worksheets("Dataset").Range("B2:F9").Copy Workbooks("Destination Workbook").Worksheets("Sheet1").Range("B2")
End Sub

Your code is now ready to run.

• Next, Run this code.

Data from the Dataset sheet in the Source Workbook is now copied in the Sheet1 sheet in the Destination Workbook.

Read More: Excel VBA: Copy Cell Value and Paste to Another Cell

14. Macro to Reproduce Data from One Sheet to Another Sheet in Another Open and Saved Workbook

This time, we will copy the data from the Dataset sheet from the Source Workbook and paste it into the Sheet2 worksheet in the Destination Workbook. But now, the workbook is open and saved.

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 CopyOneFromAnotherSaved()
    Workbooks("Source Workbook.xlsm").Worksheets("Dataset").Range("B2:F9").Copy _
    Workbooks("Destination Workbook.xlsx").Worksheets("Sheet2").Range("B2")
End Sub

Your code is now ready to run.

• Next, Run this code.

Data from the Dataset sheet in the Source Workbook is now copied in the Sheet2 sheet in the Destination Workbook. And look at the name, this workbook was saved this time.

Read More: How to Copy and Paste in Excel Without Changing the Format

15. Apply VBA to Copy and Paste Data from One Worksheet to Another Worksheet in Another Closed Workbook

In the previous two sections, we learned how to copy and paste data from one worksheet to another in another workbook that is open. In this section, we will learn the code on how to copy and paste data when the workbook is closed.

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 CopyOneFromAnotherClosed()
'To open the workbook saved on your system
'change the path below according to the location of your file in your system
Workbooks.Open "D:\ExcelDemy\Excel Folder\Destination Workbook.xlsx"
'Copies data from the workbook named Source Workbook and
'Paste it to the target workbook named Destination Workbook
Workbooks("Source Workbook").Worksheets("Dataset").Range("B2:F9").Copy Workbooks("Destination Workbook").Worksheets("Sheet3").Range("B2")
'Close the target workbook after saving
Workbooks("Destination Workbook").Close SaveChanges:=True
End Sub

Your code is now ready to run.

• Next, Run this code.

Even though, this time the workbook was closed but still after the code execution, data from the Dataset sheet in the Source Workbook is now copied in the Sheet3 sheet in the Destination Workbook.

Read More: Excel VBA to Copy Data from Another Workbook without Opening

Things to Remember

• Methods 1 to 14 requires your workbooks to be opened. When executing the macro codes shown in those methods, don’t forget to keep both the source and destination workbooks open.
• While your workbooks are saved then write the file name with the file type inside the code. When the workbooks are not saved, then write only the file name without the type of the file. For example, if your workbook is saved, then write “Destination.xlsx”, but if the workbook is not saved, then write “Destination” inside the code.

Conclusion

This article showed you how to copy and paste data from one worksheet to another in Excel with VBA. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.

Related Articles

• VBA Code to Compare Two Excel Sheets and Copy Differences
• [Fixed]: Right Click Copy and Paste Not Working in Excel (11 Solutions)
• How to Copy Multiple Cells to Another Sheet in Excel (9 Methods)
• Difference Between Paste and Paste Special in Excel
• How to Copy Alternate Rows in Excel (4 Ways)
• Copy and Paste in Excel and Keep Cell Size (7 Examples)
• How to Copy and Paste in Excel without Formulas (7 Easy Tricks)