VBA paste special | Value,Formula,Format,and Validation or all

vba-paste-special

In this tutorial, we are going to learn how to use the paste special feature in excel VBA.

Why paste special?

The way copy-pasting works in excel is, paste method copies and pastes all the properties of a cell say format, value, validation, formula, etc. So if we want only one property of a cell to be copy-pasted, say you want only formula or value to be pasted in that scenario we use paste special.Below is the list of options in which paste special can be used for,

vba-paste-special
VBA paste special values only:

In this method, we are going to copy and paste values using paste special function in VBA. To achieve this, we are going to use paste special method along with xlPasteValues property like the one below.

Range("A1").Copy
Range("C1").pastespecial xlPasteValues
Application.CutCopyMode = False

In the above code, what we are trying to do is copy the cell A1 and paste it in cell C1. 

Note:  Application.CutCopyMode is used to hide the marching ants after copying or to clear the clipboard.

Result of the above code:
vba-paste-special

As you can see, the above code copied only the value and ignored formats and validation.

VBA paste special formula only:

This method is the same as copying values but instead, we focus on copying formula rather than value. For this, we will use xlPasteFormulas property to copy only the formulas and ignore rest.

Range("A1").Copy
Range("C1").pastespecial xlPasteFormulas
Application.CutCopyMode = False
Output:
vba-paste-special

As you can see, in the formula bar we copied only the formula instead of value as we did before.

VBA paste special format only:

This method mainly focuses on copying only the styling properties of a cell-like a font, background color, foreground color, borders, etc. but not the cell width.

Range("A1").Copy
Range("C1").pastespecial xlPasteFormats
Application.CutCopyMode = False
vba-paste-special

As you can see the above code only pasted the format and ignored text inside the cell.

VBA paste special column width only:

This method mainly focuses on copying only the column width and ignores the rest of all the things.

Range("A1").Copy
Range("C1").pastespecial xlPasteColumnWidths
Application.CutCopyMode = False
vba-paste-special
VBA paste special cell validation:

This method mainly focuses on copying only the cell validation. Cell validation may be used in one or more cell adding manually for each cell is a difficult task using this method we can copy-paste cell validation to other cells easily. Below code shows how to copy cell validation.

Range("A1").Copy
Range("C1").pastespecial xlPasteValidation
Application.CutCopyMode = False
vba-paste-special
VBA paste special all:

This method mainly focuses on copying all the properties of cell-like style, width, value, formula, validation, etc.

Range("A1").Copy
Range("C1").pastespecial xlPasteAll
Application.CutCopyMode = False
vba-paste-special

Hope you liked this article, if so please share it with your friends.

Share on:
Most Popular