VBA Paste Values

Excel VBA Paste Values

Copy & Paste isn’t the greatest job in the world!!! But copying and pasting with special option 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.

Examples of Paste Values in VBA

Before I tell you the paste special in VBA let me show the example in below worksheet.

Example #1 – Using Paste Special in VBA

For an example look at the below worksheet image.

In the cell, B6 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 value of 22,761 but I will get the corresponding formula.

Since there was a formula in B6 it copied the excel formula and pasted in C6 cell. In order to have only values from the copied cell, we need to do paste special method.

Step 1: Copy the cell B6.

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

Step 3: After selecting the cell press ALT + E + S, it will open up the Paste Special dialogue box.

Step 4: In the paste special box select “Values” as the option.

Step 5: Click on Ok, we should get only 22761 as the value in cell C6.

If you notice cell C6 we got only values nothing else. Since we have used VBA Paste Special Values it has ignored all the formatting as well.

To carry out the same thing in VBA we need coding knowledge. We will show you how to do it in VBA. Follow below steps.

We will do the same thing as we did in the previous steps. To copy the cell B6 use the code as Range (“B6”).Copy.

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

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

Again we got the formula here.

To perform Paste Special method let’s look at the below syntax of Paste Special method in VBA.

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 the let’s break the code into two lines.

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 “Paste Special” method.

After selecting the method hit 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

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

This is exactly same as we did in the worksheet.

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 executed.

Example #2 – Paste Special with Loops

Paste special easy but in terms of using this as part of large code requires an advanced level of coding skills.

For an example look at the below image.

In the above image of the worksheet in column “F” we have 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.

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 VBA paste values from the copy of 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 in VBA using paste special along with examples and download excel template. Below are some useful excel articles related to VBA –

• Double Data Type in Excel VBA
• Excel VBA Counter
• VBA Asc Function
• Excel VBA DateSerial
• VBA Paste Data
• VBA Copy Paste
• Pause VBA code
• VBA File Copy Function