In this article, I will show you various ways to copy and paste data using Excel VBA. The paste method is one of the most commonly used methods in Excel. There are two methods for pasting data, namely “Worksheet.Paste” and “Range.PasteSpecial“. Let us start with the “Worksheet.Paste” method. Before we look at these, let’s have a quick look at the “Range.Copy” Method

Range.Copy Method

This method can be used to perform a simple single line copy/paste operation or to just copy the specified range to clipboard.

Syntax: expression . Copy (Destination)

expression – A variable that represents a Range object that needs to be copied

Destination – Specifies the new range to which the specified range will be copied. If this argument is omitted, the range is copied to the Clipboard.

Example 1: Using the Range.Copy Method

See the various usages of this function in the code below. You can follow along with the help of the comments in the code.

Sub rangeCopy()

    'Copy range to clipboard
    Range("A1:A10").Copy

    'Examples of Single line copy paste within a sheet
    'Copy and paste a single cell
    Range("A1").Copy Range("B1")

    'Copy and paste a range
    Range("A2:A5").Copy Range("B2:B5")

    'For a range, the destination can simply be a starting cell
    Range("A6:A10").Copy Range("B6")

    'Copy to another sheet
    Worksheets("Sheet1").Range("A1:A10").Copy Worksheets("Sheet2").Range("A1")

    'Copy to another workbook
    Workbooks("Source.xlsm").Worksheets("Sheet1").Range("A1").Copy _
    Workbooks("Destination.xlsx").Worksheets("Sheet1").Range("A1")

End Sub

Worksheet.Paste method
This method will simply paste the contents of the clipboard onto the specified worksheet. It is equivalent to the paste operation using Ctrl + V. You need to use the Range.Copy Method before the paste method (unless, of course, you are copying the data in some other way)
Syntax: expression . Paste (Destination , Link )
where
expression – A variable that represents a Worksheet object (for instance, ActiveSheet)
Destination (Optional) – A Range object that specifies where the Clipboard contents should be pasted. If this argument is omitted, the current selection is used. If this argument is specified, the Link argument cannot be used.
Link (Optional) – True to establish a link to the source of the pasted data. If this argument is specified, the Destination argument cannot be used. The default value is False.
Let us see the usage of this method. For all the subsequent examples, the source data to be copied is shown below.  The amount column consists of formulas. All the rest are plain text

Example 2: Paste a range with text
Sub worksheetPasteValues()

    Worksheets("Sheet1").Range("C2:D12").Copy

    'Data will be pasted along with formatting
    ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range("J2:K12")

End Sub

This is how the output will look:

Example 3: Paste a range that contains formulas
Assume that Column G contains formulas. When you are trying to copy this range, the destination will contain formulas and not the value. We will see how to paste only values shortly.
Sub worksheetPasteFormulas()

    'Copied column contains formulas
    Worksheets("Sheet1").Range("G2:G12").Copy
    'Paste method will copy the formulas and paste then in column J along with the formatting
    ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range("J2:J12")

End Sub

The output will look like this. Notice the formula bar that shows the copied formula

Example 4: Pasting link to source data
Setting Link = true will just create a link to the source range in the destination range (no formatting is copied). Because the destination parameter cannot be used when link is specified, you need to select (activate) a cell where you want to paste the data (or else the data will be pasted on the current active cell)
Sub worksheetPasteLinks()    

    Worksheets("Sheet1").Range("F2:F12").Copy
    'Select the cell where you want to paste the data
    ActiveSheet.Range("J2").Activate
    'So, J2 will have the formula "=F2" and so on
    ActiveSheet.Paste Link:=True

End Sub

Here is the output. All cells in column J will have formulas as you can see below

Range.PasteSpecial method
This method gives you more control on what part of the data is pasted and how it is pasted. It corresponds to the options available in the Paste Special context menu that appears after you paste data in Excel using Ctrl + V

Syntax: expression . PasteSpecial (Paste , Operation , SkipBlanks , Transpose)
where
expression – A variable that represents a Range object.
Paste (Optional) – XlPasteType – Specifies the part of the range to be pasted. For instance, values, formats, formulas and so on. We will cover examples on the most commonly used paste types in the following section. For a complete list, refer to XlPasteType Enumeration (Excel)
Operation (Optional) – XlPasteSpecialOperation – Specifies the paste operation. For instance, add to or subtract from values in destination and so on. For a complete list, refer to XlPasteSpecialOperation Enumeration (Excel)
SkipBlanks (Optional) – True to have blank cells in the range on the Clipboard not be pasted into the destination range. Please note here that the pasted data is of the same size. Excel will just not overwrite existing data (in the destination range) with blank cells from copied source. The default value is False .
Transpose (Optional) – True to transpose rows and columns when the range is pasted.The default value is False .
The available paste types are:



xlPasteAll
Everything will be pasted


xlPasteAllExceptBorders
Everything except borders will be pasted


xlPasteAllMergingConditionalFormats
Everything will be pasted and conditional formats will be merged


xlPasteAllUsingSourceTheme
Everything will be pasted using the source theme


xlPasteColumnWidths
Copied column width is pasted


xlPasteComments
Comments are pasted.


xlPasteFormats
Copied source format is pasted.


xlPasteFormulas
Formulas are pasted


xlPasteFormulasAndNumberFormats
Formulas and Number formats are pasted


xlPasteValidation
Validations are pasted


xlPasteValues
Values are pasted


xlPasteValuesAndNumberFormats
Values and Number formats are pasted



Example 6: Copying values only using Paste Special
xlPasteValues will paste only the values. The formulas will not be pasted and the formatting of the destination range will be retained.
Sub PasteSpecialValues()

    Range("B2:G12").Copy
    'Paste only the values (no formats and formulas will be pasted)
    Range("J2").PasteSpecial Paste:=xlPasteValues

    'Clear the clipboard contents
    Application.CutCopyMode = False

End Sub


It is always a good practice to clear the clipboard contents after your paste operation is over. The last line in the above code
Application.CutCopyMode = False
will clear the clipboard contents and hence, disable the marching ants around the copied range.
Here is the output containing only values and no formulas

Example 7: Copying values only using Transfer Method
If you need to just copy the values over, the transfer method (using resize) should be preferred over paste special. The advantages being speed (especially for a large amount of data) and secondly, the clipboard is not used at all (so no errors occur if the clipboard contents change just before the paste operation and there are no data leaks). Here is the code equivalent to the Example# 6:
Sub TransferValuesUsingResize()

    Dim rng As Range

    'Set the source range
    Set rng = Range("B2:G12")

    'Transfer Values to another range
    Range("J2").Resize(rng.Rows.Count, rng.Columns.Count).Cells.Value = rng.Cells.Value

End Sub


Note that the source and destination ranges must be of the same size if you are to use the transfer method.
Example 8: Pasting formats only
Sub PasteSpecialFormats()

    Range("B2:G12").Copy
    'Paste only the formats including number formats, background and font color, borders and so on
    Range("J2").PasteSpecial Paste:=xlPasteFormats

    'Clear the clipboard contents
    Application.CutCopyMode = False

End Sub

This is how the data will look before and after running the code



Example 9: Paste Formulas
This will paste the formulas from the source range.
Sub PasteSpecialFormulas()

    Range("B2:G12").Copy
    'Paste formulas from cells that contain formulas
    'For remaining cells values will be copied
    'Formatting is not copied
    Range("J2").PasteSpecial Paste:=xlPasteFormulas

    Application.CutCopyMode = False

End Sub

Notice the formula that has been copied in the formula bar in the output below:

Example 10: Transpose data
If you want to transpose the rows and columns, use the code below.
Sub PasteSpecialTranspose()

    Range("B2:G12").Copy
    'Rows and columns are transposed
    'Formulas and formatting are retained
    Range("J2").PasteSpecial Transpose:=True

    Application.CutCopyMode = False

End Sub

This is how the output will look like:

You can also specify the paste type to control what part of the data will be copied as in the previous examples. So, in the above example if you want to transpose values only, change the pastespecial to:
 Range("J2").PasteSpecial Paste:=xlPasteValues, Transpose:=True 
And the output will be

Operations:
Now let us look at how to use the operation parameter with paste special. Here are the operations available.
xlPasteSpecialOperationAdd         – Copied data will be added to the value in the destination cell.

xlPasteSpecialOperationDivide     – Copied data will divide the value in the destination cell.

xlPasteSpecialOperationMultiply  – Copied data will multiply the value in the destination cell.

xlPasteSpecialOperationNone       – No calculation will be done in the paste operation. (Default)

xlPasteSpecialOperationSubtract  – Copied data will be subtracted from the value in the destination cell.
Example 11: Paste Special Add Operation
Say you have a price column and another column with markup as seen below

If you want to add the current price to the corresponding markup in column D (i.e. D3 = B3 + D3 and so on), you can use the Operation xlPasteSpecialOperationAdd as shown below:
Sub AddOperation()

    With Worksheets("Sheet1")
        .Range("B3:B12").Copy
        .Range("D3:D12").PasteSpecial Operation:=xlPasteSpecialOperationAdd
        .Range("D2").Value = "New Price <pre wp-pre-tag-10=""></pre>quot;
    End With

    Application.CutCopyMode = False

End Sub

The newly added values for column D will then look like this: