Paste Values using a VBA Code in Excel

In Excel, by using a VBA code, there are two ways to paste values from one cell or a range to another. Out of these two, the best and most reliable method is to use the paste special, allowing you to only paste values from one cell to another.

Let’s learn to write a code for this.

Only Paste Values using a VBA Code.

The example below shows a value in cell A1 with the cell color and bold formatting. Now, to paste these values in cell A3, we need to write a code:

1. Refer to the cell from where you want to copy the values.
2. Now, type a dot (.) and enter the copy method.
3. After that, refer to the cell from where you want to paste the values only.
4. Next, enter the paste special method using a dot (.).
5.  Finally, enter and start parentheses and select the (xlPasteValues) from the drop-down.

You can see when you run the code to copy the value from cell A1 and paste only values to A3.

Sub paste_values()
Range("A1").Copy
Range("A3").PasteSpecial (xlPasteValues)
End Sub

In this code, we have two lines of code. And it’s easy to understand how this code works.

The first line of the code copies the value from the cell A1. Now, here, you need to understand that when you copy a cell, it copies everything from that cell along with the value.

That’s why we need to use the paste special method in the second line to get the value only. In the paste special method, you can see multiple options to paste in the destination cell or a range.

You can also use the argument in the following way:

In the above example, we have used a numeric value to define and paste values to the source range.

Sub paste_values()

Range("A1").Copy

Range("A3").PasteSpecial Paste:=-4163

End Sub

Paste Values in a Range with a VBA Code

If you want to paste values to a range of cells, in that case, you need to refer to the range instead of a single cell.

Sub paste_values_2()

Range("A1").Copy

Range("A3:A7").PasteSpecial (xlPasteValues)

End Sub

We have referred to an entire range as a source in the above code. So the code copies the value from the cell A1 and then pastes it to the range A3:A7.

In the below example, we have copied from a range of cells and pasted it to a range with the same number of cells.

Sub paste_values()

Range("A1:A3").Copy

Range("B1:B3").PasteSpecial (xlPasteValues)

End Sub

As you can see in the example, this code copies values from the range A1:A3 and pastes them to the range B1:B3.

You can also write this code in a way where you need to specify the first cell of the range where you want to paste the copied values.

Sub paste_values()

Range("A1:A3").Copy

Range("B1").PasteSpecial (xlPasteValues)

End Sub

You can see that in the destination range, we have referred to cell B1, the starting cell of the range where we want to paste the values.