How to use paste special using VBA Codes? | VBA Codes | Learnitix

3 min readJul 28, 2021

Paste special is a very common functionality of excel that we use so frequently while working on the data. We may need to paste the Values, formatting, or formulas using paste special instead of pasting the data with all the components included. But how we can use paste special in excel VBA codes. Let’s understand with an example.

Suppose below is the data that has all the components included that is Values, Formatting (borders & colors ), Formulas, and comments.

Let’s copy the data from sheet5 using VBA codes.

Sub Paste_special()

Sheets(“Sheet5”).select

Range(“a1”).currentregion.copy

End Sub

The above codes will copy the data from sheet5 and let’s say we want to paste it into sheet4.

Sub Paste_special()

Sheets(“Sheet5”).select

Range(“a1”).currentregion.copy

Sheets(“Sheets4”).select

End Sub

Sheets(“Sheets4”).select — This code will activate the sheet4 on which we want to paste the data.

Now select the cell and use paste special property to paste only Values.

You will see many properties that you can use in paste special. Select the one you want to use. Let’s in the example, will use “xlpastevalues” to paste only the values.

Codes:

Sub paste_special()

Sheets(“Sheet5”).Select

Range(“a1”).CurrentRegion.Copy

Sheets(“Sheet4”).Select

Range(“a1”).PasteSpecial (xlPasteValues) ‘ to paste values

Range(“a1”).PasteSpecial (xlPasteFormats) ‘ To paste the formats

Range(“a1”).PasteSpecial (xlPasteComments) ‘ To paste the comments

Range(“a1”).PasteSpecial (xlPasteFormulas) ‘ to paste the formulas

End Sub

Output: Only values got pasted in sheet4.