VBA Paste Special

Updated on December 12, 2023

Like a worksheet, we have a Paste Special method to copy data and paste it on a different cell range. It allows us to paste the data as itself or only the formulas or only the values and the same fashion. We can use Paste Special in VBA using the range property method: paste special() providing the type we want in the brackets.

Paste Special in VBA

Paste Special in excelPaste Special In ExcelPaste special in Excel allows you to paste partial aspects of the data copied. There are several ways to paste special in Excel, including right-clicking on the target cell and selecting paste special, or using a shortcut such as CTRL+ALT+V or ALT+E+S.read more serves in many ways in our daily work. Using Paste Special, we can do many more things than usual. Of course, copy and paste are everywhere in the computer world. But, Paste Special is the advanced thing in Excel.

Like regular Excel Paste Special in VBA, too, we have a Paste Special method to paste the copied data. Copying things in Excel is not strange for Excel users. They copy and paste, and most of the time, they use Paste Special to serve their purpose in many ways.

VBA Paste Special

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Paste Special (wallstreetmojo.com)

In regular Excel, paste includes many options like paste only values, paste formulas, paste formats, etc.

Paste Special

Paste Special has to paste, operate, skip blanks, and transpose like in VBA. So, we have all the parameters with the Paste Special method.

The Formula of Paste Special in VBA

Below is the formula for Paste Special in VBA.

VBA PasteSpecial

The Paste Special is available with the VBA Range objectVBA Range ObjectRange is a property in VBA that helps specify a particular cell, a range of cells, a row, a column, or a three-dimensional range. In the context of the Excel worksheet, the VBA range object includes a single cell or multiple cells spread across various rows and columns.read more because after copying the data, we will be pasting it in the cell range, so the Paste Special method is available with the Range object.

Paste Type: After copying the data, how do you want to paste it? Whether you want to paste values, formulas, formats, validation, etc. Below is the complete list of options available under Paste Type.

Option of paste type

Paste Special Operation: While pasting, do you want to perform any operations like add, subtract, division, multiplication, or none?

Paste special operation
  • [Skip Blanks]: If you want to skip blanks, you can choose TRUE or FALSE.
  • [Transpose]: If you want to transpose the data, you can choose TRUE or FALSE.

Examples of Paste Special in Excel VBA

The following are examples of Paste Special in VBA.

You can download this VBA Paste Special Template here – VBA Paste Special Template

Example #1 – Paste only Values using VBA PasteSpecial Function

In the first example, we will perform pasting only values using paste special. For example, assume below is the data you have in the sheet name called “Sales Data.”

VBA PasteSpecial Example 1

Now, we will perform the task of copying and pasting using several Paste Special methods. Follow the below steps.

Step 1: Create a Macro name first.

VBA Paste Special Example 1

Step 2: First, copy the range A1 to D14 from the sheet name “Sales Data.” To copy the range, apply the below code.

Code: 

Range("A1:D14").Copy
VBA PasteSpecial Example 1-1

Step 3: After copying the data, we will paste the values from G1 to J14. First, reference the range.

Code:

Range ("G1:J14")
VBA PasteSpecial Example 1-2

Step 4: After selecting the range, we need to paste. So, put a dot (.) and select the Paste Special method.

Code:

Sub PasteSpecial_Example1()

    Range("A1:D14").Copy
    Range("G1:J14").PasteSpecial

End Sub
VBA PasteSpecial Example 1-3

Step 5: From the dropdown list, select the option “xlPasteValues.”

Code:

Sub PasteSpecial_Example1()

   Range("A1:D14").Copy
   Range("G1:J14").PasteSpecial xlPasteValues

End Sub
VBA PasteSpecial Example 1-4

Step 6: Run this code using the F5 key or manually and see what happens.

VBA PasteSpecial Example 1-5

So, our code copied the data from A1 to D14 and pasted it from G1 to J14 as values.

It has performed the task of shortcut excel keyShortcut Excel KeyAn Excel shortcut is a technique of performing a manual task in a quicker way.read more in worksheet ALT + E + S + V.

Shortcut key to paste

Example #2 – Paste All using VBA PasteSpecial

Now, we will see what happens if we perform the task of xlPasteAll.

Code:

Sub PasteSpecial_Example2()

   Range("A1:D14").Copy
   Range("G1:J14").PasteSpecial xlPasteAll

End Sub

Now, if you run this code manually through the “Run” option, by pressing the F5 key, we will have as it is data.

VBA PasteSpecial Example 1-8

Example #3 – Paste Formats using VBA PasteSpecial Function

Now, we will see how to paste only formats. The below code would do the job for us.

Code:

Sub PasteSpecial_Example3()

   Range("A1:D14").Copy
   Range("G1:J14").PasteSpecial xlPasteFormats

End Sub
VBA PasteSpecial Example 1-10

If you run this code using the F5 key or manually, we will get the only format of the copied range, nothing else.

VBA PasteSpecial Example 1-11

Example #4 – Paste Column Width using VBA Paste Special

Now, we will see how to paste only column widthColumn WidthA user can set the width of a column in an excel worksheet between 0 and 255, where one character width equals one unit. The column width for a new excel sheet is 8.43 characters, which is equal to 64 pixels.read more from the copied range. For this, we have increased the width of one of my data columns.

VBA PasteSpecial Example 1-13

Apply the below code. It will paste only the column width of the copied range.

Code:

Sub PasteSpecial_Example3()

 Range("A1:D14").Copy
 Range("G1:J14").PasteSpecial xlPasteColumnWidths

End Sub
VBA PasteSpecial Example 1-14

Run this code and see the difference in the column width.

VBA PasteSpecial Example 1-15

Now, the “Sales” column width increases to the width of our copied range column.

Example #5 – Copy the Data from One Sheet to Another Sheet using VBA Paste Special Option

We have seen how to copy and paste the data on the same sheet. Now, we will learn how to paste from one sheet to another sheet.

Step 1: Before we select the range, we need to tell from which sheet we need to select the data.

Code:

Sub PasteSpecial_Example5()

   Worksheets ("Sales Data")

End Sub
Example 2

Step 2: After selecting the sheet by its name, we need to select the range in that sheet. They copy it.

Code:

Sub PasteSpecial_Example5()

  Worksheets("Sales Data").Range("A1:D14").Copy

End Sub
Example 2-1

The above code says in the sheet name “Sales Data” copy the Range (“A1:D14”).

Step 3: Since we are pasting it on a different sheet, we must select the sheet by its name.

Code:

Sub PasteSpecial_Example5()

  Worksheets("Sales Data").Range("A1:D14").Copy
  Worksheets ("Month Sheet")

End Sub
Example 2-2

Step 4: Now, in the sheet “Month Sheet,” select the range.

Code:

Sub PasteSpecial_Example5()

  Worksheets("Sales Data").Range("A1:D14").Copy
  Worksheets("Month Sheet").Range ("A1:D14")

End Sub
Example 2-3

Step 5: Using Paste Special, we will be pasting values and format.

Code:

Sub PasteSpecial_Example5()

  Worksheets("Sales Data").Range("A1:D14").Copy
  Worksheets("Month Sheet").Range("A1:D14").PasteSpecial xlPasteValuesAndNumberFormats

End Sub
Example 2-4

Step 6: We are not only pasting values and format using VBA Paste Special, but we are pasting it as TRANSPOSE as well.

Code:

Sub PasteSpecial_Example5()

Worksheets("Sales Data").Range("A1:D14").Copy
Worksheets("Month Sheet").Range("A1:D14").PasteSpecial xlPasteValuesAndNumberFormats, Transpose:=True

End Sub
Example 2-5

Now run this code. It will copy and transpose the data to the “Month Sheet.”

Example 2-6

Things to Remember About Excel VBA PasteSpecial Function

  • To skip blanks, we must enter the argument as TRUE by default. It takes FALSE.
  • If we want to transpose the data, we must select the transpose as TRUE.
  • We can perform only one Paste Special at a time.

Recommended Articles

This article has been a guide to Excel VBA PasteSpecial. Here, we learn the top 5 ways to use VBA PasteSpecial function, practical examples, and downloadable VBA codes. Below are some useful Excel articles related to VBA: –