EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home VBA VBA Resources VBA Tips VBA Paste Values
 

VBA Paste Values

Pradeep S
Article byPradeep S
Madhuri Thakur
Reviewed byMadhuri Thakur

VBA Paste Value

Excel VBA Paste Values

On a daily basis, we usually use copy/paste feature in excel frequently. Apart from copy/paste, we can also format data with various below-mentioned options available in paste special.

 

 

PasteSpecial

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

VBA PASTE VALUES function Pastes only the values of the copied data range as displayed in the cells.

Note: It will not copy the underlying formula present in the cell, which you find in the formula bar. i.e. it will copy only the result value of a formula.

In VB macro window, CTRL + SPACE click will help you out for VBA Intellisense Drop-down, which helps out in writing the code. After typing PASTE SPECIAL and click on spacebar below mentioned various paste type options appears which also contains xLPasteValues.

Syntax for Paste Values in Excel VBA

The syntax for VBA Paste Value function in excel is as follows:

Syntax VBA PasteSpecial

Prior to paste special values or When you are using paste special options, VBA Range object needs to be mentioned before because you are copying the data from specific cell range to another cell range.

Here, with reference to excel worksheet, the VBA range object is cells which may be either single or multiple.

XlPaste Type:

Different types of paste-type which appear under dropdown of PASTE SPECIAL are:

Description 1XlPaste operation Type:

Similar to paste type options, there is also paste operations option also where you can perform below mention mathematical operations.

Description-5

  • [Skip Blanks]: If you want to skip blanks then you can choose either of the options i.e. TRUE or FALSE.
  • [Transpose]: If you want to transpose the data then you can choose either of the options i.e. TRUE or FALSE

Suppose, I have the source data in a range G7 TO J10, which contain students score & their results with reference to registration ID, where it contains formulas, text data & numeric data.

Paste Value Example 1.1

I need to copy this data to another cell range in the same sheet without formula i.e. as a value only, for this I need to follow the below-mentioned steps.

How to Use Paste Values Function in Excel VBA?

Below are the different examples to use Paste Values Function in Excel using VBA code.

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

VBA Paste Values Function – Example #1

Step 1: Open a VB Editor window with the help of Alt + F11 shortcut key, Select or click on Visual Basic in the Code group on the Developer tab.

VBA Value Paste Example 1-1

Step 2: To create a blank module, right-click on Microsoft excel objects, in that click on Insert and under the menu section select Module, where the blank module gets created, under the (NAME) category of properties section, you can rename as “VB_PASTE_VALUES”

Code:

Sub PASTE_VALUES()

End Sub

VBA Value Paste Example 1.2

Step 3: I need to First copy the data range with the help of Range. Copy method (For copying the specific range of data) i.e. from G7 to J10 from sheet name “VB_PASTE_VALUES” for copying the range, I need to apply or add below-mentioned code after sub.

Code:

Sub PASTE_VALUES()

Range("g7:j10").Copy

End Sub

VBA Value Paste Example 1.3

Step 4: Once after copying the data, I need to paste the values from G14 to J17. Here the First reference of the range is G14, here either I can enter “G14” OR I can enter “G14 to J17”. After selecting the range, I need to paste. So, for this, put a dot (.) after range and type PasteSpecial, once you click on spacebar key, below mentioned various xlpastetype options appears, in that select xlPasteValues

Code:

Sub PASTE_VALUES()

Range("g7:j10").Copy
Range("g14").PasteSpecial xlPasteValues

End Sub

Example 1.4

Step 5: Once you run this code, it copied the data from G7 to J10 and pasted from G14 to J17 as values.

Example 1.5

Now, in the above-mentioned example, there is a difference in the table format between copied & pasted data. (Formatting changes).

Step 6: If you want the same table format, you need to update the code with below-mentioned changes, where you need to use one extra line of code xlPasteformat prior to xlPastevalues

Code:

Sub PASTE_VALUES()

Range("g7:j10").Copy
Range("g14:j17").PasteSpecial xlPasteFormats
Range("g14:j17").PasteSpecial xlPasteValues

End Sub

 Example 1.7

Step 7: When you run the above code, it will initially copy and paste the table format & later it will paste values.

VBA Paste Value Example 1.8

Note: When you run the above-mentioned codes, during the copy & paste values procedure once the data is copied, selection mode appears, you can observe it across borders with green dotted lines.

You can remove that by updating the macro with last line code before end sub command   i.e.

Application.cutcopymode = false the above code will disable marching ants around a copied range

VBA Paste Values Function – Example #2

In the first example, we did a copy and paste data in the same worksheet sheet,

Now, suppose want to copy data from one worksheet to another worksheet, let check out how we can perform this task

Step 1: In the VBA editor, I have a given a name as PASTE_VALUES3() after typing Sub

Code:

Sub PASTE_VALUES_3()

End Sub

VBA Paste Value Example 2.1

Step 2: Prior to selecting the range, we need to inform from which sheet the data needs to be selected, so the worksheet needs to be selected, after this, we need to select the range with the help of range.Copy method similar to example 1.

Code:

Sub PASTE_VALUES_3()

Worksheets("VB_PASTE_VALUES").Range("g7:j10").Copy

End Sub

VBA Value Paste Example 2.2

In the above code, the worksheet name is “VB_PASTE_VALUES”, and the data range is”g7:j10″

Step 3: In the next step we are pasting data to another worksheet, therefore we need to select that sheet by its name and range where we need to paste the data, after the range, we need to enter pastespecial function and paste type option i.e. PasteSpecial xlPasteValue

Code:

Sub PASTE_VALUES_3()

Worksheets("VB_PASTE_VALUES").Range("g7:j10").Copy
Worksheets("Sheet2").Range("A1").PasteSpecial xlPasteValues

End Sub

Example 2.3

Step 4: At the end of code, prior to end sub, we need to enter above-mentioned code, where it will disable marching ants around the copied range.

Code:

Sub PASTE_VALUES_3()

Worksheets("VB_PASTE_VALUES").Range("g7:j10").Copy
Worksheets("Sheet2").Range("A1").PasteSpecial xlPasteValues
Application.CutCopyMode = False

End Sub

VBA Value Paste Example 2.4

Step 5: Now, the code is ready, once you run the above-mentioned code, it will return below-mentioned output in sheet2, i.e. the copied data will be paste in the sheet2 from A1 cell (as paste values)

VBA Paste Value Example 2.5

Thinks to Remember

  • Only one paste special task at a time can be performed, it may be paste type or paste operation. Paste special (Paste, Operation, SkipBlanks, Transpose), In the paste special function all the four arguments are optional, where first two arguments are important through which we can control dataset values (formatting, coloring, and data validation)
  • Paste type options in paste special is important & helpful in formatting a data when you pull out data from the various databases
  • In VBA, To paste values only, there are various other options available apart from range.

Recommended Articles

This is a guide to VBA Paste Values. Here we discuss how to use Excel VBA Paste Values function along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA Copy Paste
  2. VBA Subscript out of Range
  3. VBA Web Scraping
  4. VBA PasteSpecial

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

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

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Web Development & many more.

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

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

EDUCBA

Download VBA Paste Values Excel Template

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW