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.

Macro to Copy and Paste Data from One Worksheet to Another in Excel: 15 Methods

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.

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.

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

Download Workbook

You can download the free practice Excel workbook from here.

Copy and Paste from One Worksheet to Another.xlsm

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.

Get FREE Advanced Excel Exercises with Solutions!

4 Comments

Anthony Oct 29, 2022 at 1:29 AM

Thanks! It works for me.
Question is I have 4 worksheets all with the same columns.
I need Column Z that has conflict or no conflict to be copied and pasted into one worksheet.

So look in worksheet 1, go to column Z and just copy conflicts
do this for worksheets 2, 3, 4 and then merge into one worksheet that shows all conflicts from worksheets 1,2,3,4
Is that possible?
- Reply
  Rafiul Haq Oct 30, 2022 at 2:15 PM
  Thank you Anthony for your question. Let’s assume you have this data in 4 sheets and you want to copy the rows that has conflict.
  
  Now, the output should contain only the “yes” from the 4 sheets:
  
  To copy values from 4 sheets to a new sheet, use the following VBA code
```
Option Explicit
Sub Copy_Conflict_Values_New_Sheet()
Dim xRange As Range
Dim End_Row, SheetNumber As Integer
Worksheets("Output").Range("X5:Z100").ClearContents
For SheetNumber = 1 To 4
Worksheets(SheetNumber).Activate
    For Each xRange In Range("Z4:Z" & Cells(Rows.Count, "Z").End(xlUp).Row)
    End_Row = Worksheets("Output").Range("Z" & Worksheets("Output").Rows.Count).End(xlUp).Row
    If xRange.Value = "Yes" Then
            Rows(xRange.Row).Copy Destination:=Worksheets("Output").Rows(End_Row + 1)
            End_Row = End_Row + 1
    End If
    Next
Next SheetNumber
End Sub
```
Ash Dec 18, 2022 at 7:55 PM

I have data in one worksheet, but the number of rows keeps changing. Like today it may have 13000 rows tomorrows 13900 rows and next day more. i need to copy the data to another without opening the source file. how to do that. I kept a range A2:K60000 but after 13000 it comes zero value till 60000 rows. How can i copy the exact rows to destination.

my code was

Sub CollectData()
Dim rgTarget As Range
Set rgTarget = ActiveSheet.Range(“A2:K60000”) ‘where to put the copied data.
rgTarget.FormulaArray = “=’C:\Users\Downloads\[USC]Sheet1’!$A$2:$K$60000”
rgTarget.Formula = rgTarget.Value
End Sub
- Reply
  Saquib Dec 21, 2022 at 9:48 AM
  Thank you for your question. To solve the problem, you have to follow the process and code below.
  1) Firstly, write down the VBA code below.
```
Sub Copy_Data_from_Another_Workbook()
Dim wb As Workbook
Dim newwb As Workbook
Dim rn1 As Range
Dim rn2 As Range
Set wb = Application.ActiveWorkbook
With Application.FileDialog(msoFileDialogOpen)
    .Filters.Clear
    .Filters.Add "Excel 2007-13", "*.xlsx; *.xlsm; *.xlsa"
    .AllowMultiSelect = False
    .Show
    If .SelectedItems.Count > 0 Then
        Application.Workbooks.Open .SelectedItems(1)
        Set newwb = Application.ActiveWorkbook
        Set rn1 = Application.InputBox(prompt:="Select Data Range", Default:="A1", Type:=8)
        wb.Activate
        Set rn2 = Application.InputBox(prompt:="Select Destination Range", Default:="A1", Type:=8)
        rn1.Copy rn2
        rn2.CurrentRegion.EntireColumn.AutoFit
        newwb.Close False
    End If
End With
End Sub
End With
End Sub
```
  2) By running the code, a “File Open” window will appear on your computer.
  After that, click on the workbook you want to collect data from.
  Then, click on the OK button.
  
  3) Then, select the data from the source file by dragging over the range, and then click OK.
  
  4) After selecting the data range. Now select the destination range where you want to put the data.
  And, click OK.
  
  5) In the end, this will close the source file and the data will copy on the destination file.
  
  Below is a link to an article where you will find three different methods and different types of VBA codes that will resolve your issue.
  https://www.exceldemy.com/excel-vba-copy-data-from-another-workbook-without-opening/

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

Macro to Copy and Paste Data from One Worksheet to Another in Excel: 15 Methods

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

2. VBA Macro to Copy and Paste Data from One Active Worksheet to Another in Excel

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

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

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

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

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

8. Implement Macro Code to Duplicate Data from One Worksheet to Another with USEDRANGE Property

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

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

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

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

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

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

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

Things to Remember

Conclusion

Related Articles

Leave a reply Cancel reply