VBA Paste Values

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.

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

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:

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:

XlPaste operation Type:

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

• [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.

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.

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.

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

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

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

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

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

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

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

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

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

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

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)

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. Copy Paste Function in VBA
2. Substring Excel Function
3. VBA Subscript out of Range
4. Excel ISNUMBER Formula