WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Paste Values

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?

You can download this VBA Paste Value Excel Template here – VBA Paste Value Excel Template

Example #1 – Using Paste Special

For example, look at the below worksheet image.

VBA Paste Value Example 1

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

VBA Paste Value Example 2-1

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.

VBA Paste Value Example 2-2

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.

VBA Paste Value Example 2-3

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

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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

The first one is copying cell B6.

VBA Paste Value Example 2-4

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

VBA Paste Value Example 2-5

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

VBA Paste Value Example 2-6

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

VBA Paste Value Example 2-7

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

VBA Paste Value Example 2-8

In this variety of options, select “xlPasteValues.”

VBA Paste Value Example 2-9

After selecting the option, hit the tab key to auto select.

Code:

Sub Paste_Values()

 Range("B6").Copy
 Range("C6").PasteSpecial xlPasteValues

End Sub

Example 2-10

Step 5: Run the Code

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

Example 2-11

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

Example 2-12

Example 2-13

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.

Example 3

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-1

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
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Paste Value Excel Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More