VBA Paste Values

Excel VBA Paste Values

Copy & Paste isn’t the greatest job in the world!!! But copying and pasting with special options requires some kind of knowledge in VBA. This isn’t the straight forward process as the simple copy & paste. One of the important paste special methods is “Paste Values” in VBA.

How to Paste Values in Excel using VBA?

Example #1 – Using Paste Special

For example, look at the below worksheet image.

In cell B6, we have applied the formula to calculate the total sales values from B2 to B5 cells. Now, if I copy and paste the cell B6 to C6, I will not get the value of 22,761, but I 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 the cell B6, use the code as Range (“B6”).Copy

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

As you can see after the copy, it asks, “Destination.” This is nothing but where do 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 paste special method, let’s look at the below syntax of the paste special method.

In the paste special method, we have several methods. Based on the operation we are doing, we need to select the type accordingly.

To understand, let’s break the code into two lines.

The first one is copying cell B6.

Now in the next line, write the destination cell as Range (“C6”)

Now to access Paste Special Method put a dot and start typing the letter “P.”

In the above variety of options, select the “Paste Special” method.

After selecting the method, hit the space key to see the various paste special methods.

In this variety of options, select “xlPasteValues.”

After selecting the option, hit 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 the cell B6 to the cell C6.

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

This will disable the cut copy mode after the paste special method is executed.

Example #2 – Paste Special with Loops

Paste special is easy but in terms of using this as part of a large code requires an advanced level of 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.

Now my requirement is to copy each total cell from the respective cell and paste in the column “H” with respective 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 paste special option will perform the task of copying each total cell and paste in the column “H” with respective cells.

Example #3 – Copy From Worksheet to Another

To paste values from one worksheet to another, we need to mention both the worksheet names. Below is an example of that.

Sub Paste_Values2()

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

End Sub

Recommended Articles

This has been a guide to VBA Paste Values. Here we discuss the top 3 tips to paste values using paste special method in VBA along with examples and a downloadable excel template. Below are some useful excel articles related to VBA –

• VBA Paste Data
• VBA Copy Paste
• Pause VBA code
• VBA File Copy Function
• VBA Copy Worksheet