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

We’ll use the following sample dataset to showcase how you can copy and paste values between worksheets.

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

Steps:

Press Alt + F11 on your keyboard or go to the Developer tab and select Visual Basic to open the Visual Basic Editor.

In the pop-up code window, click Insert and select Module.

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

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

Press F5 on your keyboard or select Run and Run Sub/UserForm. You can also click on the small Play icon in the sub-menu bar to run the macro.

Here’s the result.

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

Steps:

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

Run the code to get the result.

All the data from the Dataset sheet is now copied in the Paste sheet that was active before copying the data.

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

The Range sheet consists of only one value.

Steps:

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

Run this piece of code.

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

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

Steps:

Open the Visual Basic Editor from the Developer tab and Insert a Module in the code window.
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

Run this piece of code.

Data from the Dataset sheet is now transferred in the PasteSpecial sheet in Excel.

Method 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). The destination sheet Last Cell also contains some values. We will copy the data in cells B5 to F9 from the Dataset sheet and paste it below the last cell of the Last Cell sheet.

Steps:

Open the Visual Basic Editor from the Developer tab and Insert a Module in the code window.
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

Run this code,

Only the selected data from the Dataset sheet is copied below the last cell in the Last Cell sheet in Excel.

Method 6 – VBA Macro to Clear a Worksheet First, then Copy and Paste to Another Worksheet

We will clear the data from the Clear Range sheet and put in the data from the Dataset sheet.

Steps:

Open the Visual Basic Editor from the Developer tab and Insert a Module in the code window.
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

Run this piece of code.

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

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

Steps:

Open the Visual Basic Editor from the Developer tab and Insert a Module in the code window.
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

Run this piece of code.

Method 8 – Implement Macro Code to Duplicate Data from One Worksheet to Another with the USEDRANGE Property

Steps:

Open the Visual Basic Editor from the Developer tab and Insert a Module in the code window.
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

Run this piece of code.

Method 9 – VBA Macro to Copy and Paste the Selected Data from One Sheet to Another in Excel

Steps:

Open the Visual Basic Editor from the Developer tab and Insert a Module in the code window.
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

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

Run this code.

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

Steps:

Open the Visual Basic Editor from the Developer tab and Insert a Module in the code window.
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

Run this chunk of code.

Sheet13 was completely blank. The executed code pasted the copied data from the Dataset sheet in the very first cell in the Sheet13 sheet in Excel.

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

Steps:

Open the Visual Basic Editor from the Developer tab and Insert a Module in the code window.
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

Run this code.

Method 12 – Paste a Row at the Bottom of a Range While Keeping the Copied Formula

Steps:

Open the Visual Basic Editor from the Developer tab and Insert a Module in the code window.
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

Run this code.

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

Method 13 – VBA to Replicate Data from One Sheet to Another Sheet in Another Open but Unsaved Workbook

We will copy the data from the Dataset sheet from the Source Workbook file and paste it into another worksheet in another workbook named Destination Workbook that is open but not saved yet.

Steps:

Open the Visual Basic Editor from the Developer tab and Insert a Module in the code window.
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

Run this code.

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

Steps:

Open the Visual Basic Editor from the Developer tab and Insert a Module in the code window.
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

Run this code.

Method 15 – Apply VBA to Copy and Paste Data from One Worksheet to Another Worksheet in a Closed Workbook

Steps:

Open the Visual Basic Editor from the Developer tab and Insert a Module in the code window.
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

Run this code.

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, write the file name with the file type inside the code. When the workbooks are not saved, write only the file name without the type of the file. For example, if your workbook is saved, write “Destination.xlsx”, but if the workbook is not saved, write “Destination” inside the code.

Download the Workbook

Copy and Paste from One Worksheet to Another.xlsm

Get FREE Advanced Excel Exercises with Solutions!

6 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/
Youssef Nov 26, 2024 at 10:57 PM

Hello,
I need to use the Code to paste from sheet to another sheet, but I need to have paste special, may I have the code updated?
I speak about this one:

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(“AutoFulfil”)
Set wsTarget = Worksheets(“FolowUp”)
‘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(“B2:l3” & iSourceLastRow).Copy wsTarget.Range(“B” & iTargetLastRow)
End Sub

Many thanks
Regards
- Reply
  Shamima Sultana Nov 27, 2024 at 6:23 PM
  Hello Youssef
  
  You can modify your code to use “Paste Special” for the paste operation. Specifically, you can use PasteSpecial to paste only values, formats, or any other attributes you need. Here’s an updated version of your code with the PasteSpecial method applied:
```
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("AutoFulfil")
    Set wsTarget = Worksheets("FolowUp")
    
    ' 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 range
    wsSource.Range("B2:L" & iSourceLastRow).Copy
    
    ' Paste data into the target sheet using PasteSpecial
    wsTarget.Range("B" & iTargetLastRow).PasteSpecial Paste:=xlPasteValues ' To paste values only
    wsTarget.Range("B" & iTargetLastRow).PasteSpecial Paste:=xlPasteFormats ' To paste formats
    
    ' Optionally, clear the clipboard after pasting
    Application.CutCopyMode = False
End Sub
```
  Explanation of changes:
  wsSource.Range(“B2:L” & iSourceLastRow).Copy: This line still copies the range from the source sheet.
  wsTarget.Range(“B” & iTargetLastRow).PasteSpecial Paste:=xlPasteValues: This pastes only the values from the copied range.
  wsTarget.Range(“B” & iTargetLastRow).PasteSpecial Paste:=xlPasteFormats: This ensures that the formatting is also pasted.
  Application.CutCopyMode = False: Clears the clipboard and removes the “marching ants” effect after the copy-paste operation.
  
  This code will now paste both the values and formats from the source sheet to the target sheet below the last used row in column B. Let me know if you need any further assistance!
  
  Regards
  ExcelDemy

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

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

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

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

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

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

Method 6 – VBA Macro to Clear a Worksheet First, then Copy and Paste to Another Worksheet

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

Method 8 – Implement Macro Code to Duplicate Data from One Worksheet to Another with the USEDRANGE Property

Method 9 – VBA Macro to Copy and Paste the Selected Data from One Sheet to Another in Excel

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

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

Method 12 – Paste a Row at the Bottom of a Range While Keeping the Copied Formula

Method 13 – VBA to Replicate Data from One Sheet to Another Sheet in Another Open but Unsaved Workbook

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

Method 15 – Apply VBA to Copy and Paste Data from One Worksheet to Another Worksheet in a Closed Workbook

Things to Remember

Related Articles

Leave a reply Cancel reply