VBA PasteSpecial

PasteSpecial in VBA

In this article, we will see an outline on VBA PasteSpecial. When we work with data in excel some data are obtained by formulas. These formulas are from references and when we paste this data to another sheet or any other workbook what happens? Because the formula is a reference and the new sheet doesn’t have the reference for the formula the value pasted is shown as an error. So in these situations what we do is paste the values as special values and what it does is that it does not paste the formulas in the target worksheet rather than pasting the value generated by the formula in the target worksheet. Similarly, in VBA there is a method to paste the values which are known as Paste Special Method. We will learn in this article how to paste as values for a single cell to the entire column and then to paste in another worksheet also we will learn the difference of (.Value) and (.Paste)method.

How to Use the PasteSpecial Method in Excel VBA?

The following examples will teach us how to use the PasteSpecial method in Excel by using the VBA Code.

Example #1 – VBA PasteSpecial

Let us begin with the basic step in this example when we paste special for a single cell in VBA. For this, follow the below steps:

Step 1: Insert a new module inside Visual Basic Editor (VBE). Click on Insert tab > select Module.

Step 2: In the module start a subprocedure.

Code:

Sub Example1()

End Sub

Step 3: Let us copy the value we have in cell A1 using the range property method.

Code:

Sub Example1()

Range("A1").Copy

End Sub

Step 4: Now let us use the paste special method to paste value we copied in cell B1.

Code:

Sub Example1()

Range("A1").Copy
Range("B1").PasteSpecial Paste:=xlPasteValues

End Sub

Step 5: When we execute the above code we can see the result in the cell B1.

Example #2 – VBA PasteSpecial

In the previous example, we copied value from a cell and paste it to an adjacent cell. Now let us copy a value from a range and paste it to another sheet. In this example, we have a value in cell A1 which is in sheet 2 and we want to paste it in sheet 2 to cell B1. For this, follow the below steps:

Step 1: We will work in the same module we had inserted as Module1 and start our subprocedure as Example 2.

Code:

Sub Example2()

End Sub

Step 2: Now let us copy the value from sheet 2 and cell A1 by using the paste method.

Code:

Sub Example2()

Sheets("Sheet2").Range("A1").Copy

End Sub

Step 3: The next step is to paste the values in sheet 3 using the paste special method in the cell B1.

Code:

Sub Example2()

Sheets("Sheet2").Range("A1").Copy
Sheets("Sheet3").Range("B1").PasteSpecial Paste:=xlPasteValues

End Sub

Step 4: Before we run the above procedure let us check what we have as Value in Cell A1 for sheet 2

Step 5: Now run the code and see the result in sheet 3.

Example #3 – VBA PasteSpecial

Earlier we copied a cell from one cell to another and from one worksheet to another. In this example, we will copy an entire column and paste it to another column. For this, follow the below steps:

Step 1: The defining of the subprocedure will be the same as for all the codes above, let us begin right below the example 3 in the same module.

Code:

Sub Example3()

End Sub

Step 2: Since we are working in sheet 4 now so it is necessary to activate sheet 4 before we make any changes to avoid confusion and error.

Code:

Sub Example3()

Worksheets("Sheet4").Activate

End Sub

Step 3: Now we will copy the values in column A using the copy method.

Code:

Sub Example3()

Worksheets("Sheet4").Activate
Columns("A").Copy

End Sub

Step 4: And we will paste the copied column to column B using the paste special method.

Code:

Sub Example3()

Worksheets("Sheet4").Activate
Columns("A").Copy
Columns("B").PasteSpecial Paste:=xlPasteValues

End Sub

Step 5: Run this code by hitting F5 or the Run button. We can see that the values from column A have been copied and pasted to column B.

Example #4 – VBA PasteSpecial

Now we have some data in Sheet 5 as follows, which shows the percentage of marks obtained by the students. For this, follow the below steps:

Step 1: In the same module below example 3 we will declare our another procedure named as Example 4.

Code:

Sub Example4()

End Sub

Step 2: Now let us activate sheet 5 first to use its properties.

Code:

Sub Example4()

Worksheets("Sheet5").Activate

End Sub

Step 3: Copy the entire range from A1 to D6 and paste it sheet 6 from cell A7 to D6 by the following code.

Code:

Sub Example4()

Worksheets("Sheet5").Activate
Sheets("Sheet5").Range("A1:D6").Copy
Sheets("Sheet6").Range("A1:D6").PasteSpecial Paste:=xlPasteValues

End Sub

Step 4: Run this code by hitting F5 or Run button. we get the following result in sheet 6.

We have seen in both the above examples in example 3 and example 4, when we paste the values the format is changed because the formulas are not pasted.

Let us look through both the example 3 and 4 in example 5 as follows.

Example #5 – VBA PasteSpecial

Code:

Sub Example5()

Worksheets("Sheet4").Activate
Columns("A").Copy
Columns("B").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Worksheets("Sheet5").Activate
Sheets("Sheet5").Range("A1:D6").Copy
Sheets("Sheet6").Range("A1:D6").PasteSpecial Paste:=xlPasteValuesAndNumberFormats

End Sub

Now when we run the above code we can see the difference in our result for example 3 and 4 as follows:

Result for Example 3.

Result for Example 4.

This method not only copies and paste the values but also keep the formatting of the values intact.

Conclusion

We discussed above for example 3 and example 4 that the format for our result changes when we use the paste special method. In paste special method we have another option to paste numbers and formats. PasteSpecial is a type of worksheet function when we copy and right-click on any cell to paste the values we get three options one is the normal paste function other being paste values and one is the paste special. Similar to the worksheet function we have to paste special in VBA too. Paste special function can be used for wide ranges in VBA.

Things to Remember

There are few things which we need to remember in using a range variable:

1. Paste Special is a worksheet function which is also used in VBA.
2. Paste Special does not paste the formulas it only copies the value generated by the formulas.
3. Paste special does not keep the original formatting.
4. Paste Values and number formats in a type of Paste special also keeps the formatting of the numbers.

Recommended Articles

This is a guide to the VBA PasteSpecial. Here we discuss how to use the PasteSpecial Method in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA SendKeys
2. VBA On Error Goto
3. VBA Input
4. VBA RGB