Excel VBA Paste: A Million Ways to Paste from Point A to Point B
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
- Example 1: Using the Range.Copy Method
- Worksheet.Paste method
- Example 2: Paste a range with text
- Example 3: Paste a range that contains formulas
- Example 4: Pasting link to source data
- Range.PasteSpecial method
- Example 6: Copying values only using Paste Special
- Example 7: Copying values only using Transfer Method
- Example 8: Pasting formats only
- Example 9: Paste Formulas
- Example 10: Transpose data
- Example 11: Paste Special Add Operation
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
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 )
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
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
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)
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
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 $" End With Application.CutCopyMode = False End Sub
The newly added values for column D will then look like this: