VBA Paste Values

Copy and paste is not the greatest job in the world! But copying and pasting with special options requires some knowledge in VBA. It is not a straightforward process as a simple copy and paste. One of the important paste special methods is “Paste Values” in VBA.

Example #1 - Using Paste Special

Look at the below worksheet image.

In cell B6, we applied the formula to calculate the total sales values from B2 to B5. Therefore, if we copy and paste cell B6 to C6, we will not get the value of 22,761, but we will get the corresponding formula.

To carry out the same thing in VBA, we need coding knowledge. We will show you how to paste values using VBA. Follow the below steps.

Step 1: Copy the cell B6.

To copy cell B6, use the code Range (“B6”).Copy

Step 2: Select the destination cell. In this case, C6 cell.

As you can see after the copy, it asks, "Destination." It is nothing but where you want to paste, so select the "Destination" as Range ("C6").

Code:

Sub Paste_Values()  Range("B6").Copy Range("C6") End Sub

Step 3: Run the Code

Run this code we will get the B6 copy in C6.

We got only the formula here.

Step 4: Perform a Paste Special Method.

To perform the PasteSpecial method, let us look at the syntax below the PasteSpecial method.

In the PasteSpecial method, we have several methods. First, based on our operation, we must select the type accordingly.

To understand, let us break the code into two lines.

The first one is copying cell B6.

In the next line, write the destination cell as Range ("C6").

Now, to access the Paste Special method, put a dot and start typing the letter "P."

Select the "Paste Special" method in the above variety of options.

After selecting the method, press the space key to see the various Paste Special methods.

In this variety of options, select "xlPasteValues."

After selecting the option, press the "Tab" key to auto-select.

Code:

Sub Paste_Values() Range("B6").Copy Range("C6").PasteSpecial xlPasteValues End Sub

Step 5: Run the Code

Now, run the code. We should get only the value of cell B6 to cell C6.

If you notice the worksheet after running the code, it is still in the copy mode.

After executing the Paste Special method, it will disable the cut copy mode.

Example #2 - Paste Special with Loops

Paste Special is easy, but using this as part of a large code requires advanced coding skills.

For example, look at the below image.

In the above image of the worksheet in column "F," we have a total column, i.e., in F2, F5, F8, F11, and F14 cells.

We must copy each total cell from the respective cell and paste it into the column "H" with the individual cells.

Using the below code with VBA loops, we can do this.

Code:

Sub Paste_Values1() Dim k As Integer Dim j As Integer j = 2 For k = 1 To 5   Cells(j, 6).Copy   Cells(j, 8).PasteSpecial xlPasteValues   j = j + 3 Next k End Sub

This code with the Paste Special option will perform the task of copying each total cell and pasting in the column "H" with individual cells.

Example #3 - Copy From Worksheet to Another

We need to mention both worksheet names to paste values from one worksheet to another. Below is an example of that.

Sub Paste_Values2() Worksheets("Sheet1").Range("A1").Copy Worksheets("Sheet2").Range("A15").PasteSpecial xlPasteValues End Sub