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.
How to Paste Values in Excel using VBA?
Example #1 – Using Paste Special
For an 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”)
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 paste special method let’s look at the below syntax of 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.
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.
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 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.
Using below code, with VBA loops, we can do this.
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
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 downloadable excel template. Below are some useful excel articles related to VBA –