PasteSpecial function in VBA
Normally, as you copy/paste a cell or range of cells in Excel, it pastes value, formatting (font color, interior color, italic, bold, etc.), Excel formulas, comments, etc. in the destination cells.
In certain scenarios, you may need to paste only the values to the destination cell rather than font color, backgrounds, or formulas.
This is where the PasteSpecial option works in Excel as well as VBA macros.
Syntax:
We will show you how to use the PasteSpecical function in VBA code with various arguments and paste options.
An example of pasting only values
Our sample sheet for this example contains cells with interior color. Whereas, the text color of a few cells is red and blue.
By using PasteSpecial function, we will only copy/paste values to the destination cells in the same sheet.
VBA code:
Sub pasteSpe_ex()
Dim rng_paste As Range
Dim x
Set rng_paste = Range("B5:D9")
rng_paste.Copy
'Pasting only values to the destination range
Range("G10:I15").PasteSpecial Paste:=xlPasteValues
End Sub
Result:
You can see in the above code that after using the range method in the source range, we used the PasteSpecial function with the Paste argument.
It enabled us to copy only values, while font and interior colors were not pasted.
Copy only formatting example
This time, we will only copy formatting to the destination range. Values are not copied. Have a look at the same sheet we used above:
Sub pasteSpe_ex()
Dim rng_paste As Range
Set rng_paste = Range("B5:D9")
rng_paste.Copy
'use xlPasteFormats for copying formats only
Range("G10:I15").PasteSpecial Paste:=xlPasteFormats
End Sub
Output:
You can see, the background/interior colors of the destination cells are the same as the source.
To show the font color, we wrote some text in the pasted cells, and you see it’s also copied.
Pasting formulas only
- For this example, we have another sheet that contains the VLOOKUP formula in the E14 cell.
- We will copy it to the G16 cell and our task is to only copy the formula of the cell – no formatting or text.
- Have a look at the code and output:
Sub pasteSpe_ex()
Dim rng_paste As Range
Set rng_paste = Range("D14")
rng_paste.Copy
'use xlPasteFormulas to copy formula only
Range("F14").PasteSpecial Paste:=xlPasteFormulas
End Sub
Result:
The source cell contains the VLOOKUP formula, and has interior color, bold, italic, and underlined – however, the destination cell only contains the formula.
What paste options are provided?
Following XlPasteType are available:
| Name | Value |
|---|---|
| xlPasteAll | -4104 |
| xlPasteAllExceptBorders | 7 |
| xlPasteAllMergingConditionalFormats | 14 |
| xlPasteAllUsingSourceTheme | 13 |
| xlPasteColumnWidths | 8 |
| xlPasteComments | -4144 |
| xlPasteFormats | -4122 |
| xlPasteFormulas | -4123 |
| xlPasteFormulasAndNumberFormats | 11 |
| xlPasteValidation | 6 |
| xlPasteValues | -4163 |
| xlPasteValuesAndNumberFormats | 12 |
Learn more about each option here:
https://learn.microsoft.com/en-us/office/vba/api/excel.xlpastetype
