Paste Special Method in VBA

In the previous article, we learned to copy and paste cells with the help of VBA. The method pastes everything including the cell content, formulas, and formats. The Paste Special method in VBA allows us to specifically decide what component of the copied range we wish to paste.

So let us start learning.

Paste Special in VBA

Paste Special in VBA serves many works in the daily Excel work. It is one of the advanced tools in Excel. In MS Excel, we can open the Paste Special dialog box.

We can achieve the full functionality of paste special using VBA. Paste Special can Paste Values, Formulas, Formats, Formulas and Number Formats, Values and Number Formats, Column Widths, and much more.

Syntax – PasteSpecial Method

The PasteSpecial method is used with the destination range object where we wish to paste the result. It has the following syntax.

Range.PasteSpecial

The PasteSpecial method has four optional arguments.

• [Paste] – This specifies the paste special way that how we wish to paste the copied range. It can have 12 values.

• [Operation] – This specifies whether we wish to perform any operation while pasting.

• [Skip Blanks] – We can choose TRUE or FALSE in order to skip blanks.
• [Transpose] – We can choose TRUE or FALSE if we wish to transpose the data.

Examples To Implement Paste Special Using VBA

In this section of the blog, we will implement some practical examples to learn the PasteSpecial method in VBA.

Example 1 – Paste Only Values using Paste Special

You can use the following macro to first copy the range A1:D4 from the worksheet “MyData”. Thereafter, the paste special will paste the values (Formulas results and No Formatting) in the worksheet named “DestinationWS”.

Sub pasteValues()
'copy the range A1:D4 in worksheet MyData
Worksheets("MyData").Range("A1:D4").Copy
'use the paste special method
'use the upper left cell of destination range
Worksheets("DestinationWS").Range("A1").PasteSpecial paste:=xlPasteValues
End Sub

When you run this Sub procedure, the range is pasted into another worksheet.

Example 2 – Paste Value and Number Formatting

We can copy and paste the values along with the number formatting of the copied range. We will use the CurrentRegion property to access the range around the source cell.

The following Sub Procedure does these tasks.

• Copies the range of cells (A1:D4) around cell A2 in the worksheet MyData of Workbook Source. In the source range, we have applied the currency number formatting on the range A2:D4.
• The PasteSpecial method pastes the values (Formula Results) along with the number formatting on the worksheet DestinedWS in a different workbook named Destination.

Sub pasteSpecialEx2()
'using paste values and number formatting
'copy the range around cell A2 in MyData
Workbooks("Source").Worksheets("MyData").Range("A2").CurrentRegion.Copy
'paste to the different workbook
Workbooks("Destination").Worksheets("DestinedWS").Range("A1") _
.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
End Sub

As a result, the values and number formatting are pasted in the range A1:D4 of the destination worksheet.

Example 3 – Paste Cell Formatting in All Worksheets

In this example, we will learn to paste the cell formatting of a range to another range of cells. Let us say we have the formatted cells in Range A1:D4 and we wish to apply this formatting to all the worksheets of the workbook in Range A1:D4.

You will use the following macro.

Sub pasteFormatsEx3()
'pasting cell formats
'copy the range from MyData worksheet
Worksheets("MyData").Range("A1:D4").Copy
'loop through all worksheets
For Each ws In Worksheets
'paste formats in each worksheet
ws.Range("A1").PasteSpecial Paste:=xlPasteFormats
Next ws
End Sub

When you run the Sub procedure, a similar formatting structure is pasted to all the existing worksheets of the workbook.

You can learn more examples of paste special.

This brings us to an end.

Thank you for reading.❤