VBA PASTE VALUES

What is Excel VBA Paste Values?

In Excel VBA (Visual Basic for Applications), “Paste Values” refers to a standard operation where you copy data from one range and paste only the values (not the formulas, formatting, or other attributes) into another range. It comes in handy when you want to retain the data itself but eliminate any underlying formulas or formatting that might be present in the original range.

For example, you want to copy a given cell value from one place to another. It can be done using VBA Paste Values by selecting the “xlPasteValues”; that is, make Paste values only in Excel.

Create a subroutine to copy the value from cells “A1” to “C1.” However, these values will still be stored in the clipboard. See more about VBA Paste Values and Formats below.

How to Paste Values in Excel using VBA?

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Examples

Let us look at the different ways of using VBA Paste Values.

Example #1

In this simple example, we look at using VBA Paste Special for different applications. Consider these two values present in your Excel WorkSheet.

• Step 1: Initialize a subroutine to copy and paste values with the PasteSpecial function.

• Step 2: Copy the cell C6.

• Step 3: Select the destination cell. In this case, D6 cell becomes

Here, only the value of cell “D6” is copied using VBA Paste Values only no formatting.

• Step 4: Perform a Paste Special Method; in this case, paste only the column width of cell “C6”.

• Step 5: Paste only the format in cell “B6”, where a value has already been declared.

Code:

Sub CopyWithPasteSpecial()
Range(“C6”).Copy
Range(“D6”).PasteSpecial xlPasteValues
Range(“E6”).PasteSpecial xlPasteColumnWidths
Range(“B6”).PasteSpecial xlPasteFormats
End Sub

• Step 6: Run the code. Then, go back to the worksheet to view the changes.

From this, the VBA Paste Values have been implemented successfully.

Example #2 – Paste Special with Loops

Consider a set of students with their percentages mentioned in Column B.

The courses they’ve selected have a certain cut-off percentage.

With the given grades, you have to determine whether they are eligible or not by printing Yes or No.

• Step 1: You start by naming the subroutine to copy all the student percentages.

• Step 2: Find the size of the table and return the number of rows.

Using the xlUp attribute, we can simulate the “up” button to find the last non-empty cell and stop after an empty cell is found.

• Step 3: Start a FOR loop going through the size of the table.

• Step 4: Along the loop, copy the values from the 2nd column and paste them in the 7th column using the VBA Cells function.

But, if we paste the values with no formatting, we get,

It is not ideal for solving the problem; hence, we modified the Paste Special format.

Instead of using the xlPasteValues, you can instead use xlPasteValuesAndNumberFormats. This is more suitable for the current problem. It is more suitable for the current problem and will print the values in percentage format as given.

• Step 5: Initialize another FOR-loop for the second table.

• Step 6: Check whether the Grades (column 7) are greater than or equal to the cut-off marks (column 6). If so, then print “Yes” in the next column. Print “No” otherwise.

Code:

Sub CopyDifferentNumberFormats()
Dim lastRow As Long
lastRow = Cells(Rows.Count, “A”).End(xlUp).Row
Dim i As Long
For i = 2 To lastRow
Cells(i, 2).Copy
Cells(i, 7).PasteSpecial xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
Next i
For cell = 2 To lastRow
If Cells(cell, 7) >= Cells(cell, 6) Then
Cells(cell, 8) = “Yes”
Else: Cells(cell, 8) = “No”
End If
Next cell
End Sub

• Step 7: Run the above code and go back to the Worksheet to see the results.

Example #3 – Copy from One Worksheet to Another

Suppose you have an Excel Table consisting of values as shown.

To convert a set of values to a table in Excel, press “Ctrl+T” to do so.

You need to copy and paste this table into another Worksheet. It can be done using VBA Paste Values. Follow the steps below to learn how to do so.

• Step 1: Name the subroutine you are going to use to copy values from one Sheet to another in the Excel WorkBook.

• Step 2: Set the given table as a ListObject variable.

• Step 3: Define the target sheet and cell value where you will paste the copied table.

• Step 4: Copy the table.

The DataBodyRange is used to copy the entire table except the header.

• Step 5: Perform the paste values only into the target range.

• Step 6: To perform VBA Paste Values and Formats as a whole, don’t add any PasteSpecial Method.

Code:

Sub CopyFromDifferentSheets()
Dim sourceTable As ListObject
Set sourceTable = ThisWorkbook.Sheets(“Example_3”).ListObjects(“Table1”)
Dim targetRange As Range
Set targetRange = ThisWorkbook.Sheets(“Example_3_1”).Range(“A1”)
sourceTable.DataBodyRange.Copy
targetRange.PasteSpecial xlPasteValues
ThisWorkbook.Sheets(“Example_3_1”).Range(“B1”).PasteSpecial
End Sub

• Step 7: Run the subroutine given above. Now go to sheet “Example_3_1” to see the pasted table values.

Paste Values only:

Paste Values and Formats:

Important Things to Note

• Ensure that you specify the correct range for both the source (copy) and destination (paste) operations.
• After pasting values, it’s a good practice to clear the clipboard using Application.CutCopyMode = False. It helps prevent unexpected behavior in subsequent copy-paste operations.
• Use the PasteSpecial method with the xlPasteValues parameter to paste only the values. It ensures that formulas, formatting, and other attributes are not copied.
• Implement error handling in VBA to handle potential issues that may arise during the copy-paste operation. It can include checking if the source range is not empty before attempting to copy.
• Explicitly reference the worksheets involved in the operation to avoid confusion, especially when working with multiple sheets. For example, use Worksheets("Sheet1").Range("A1") instead of relying on the active sheet.
• Ensure that the size of the source and destination ranges is consistent. Mismatched ranges can lead to errors or unexpected behavior.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VBA Paste Values, with it features and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA Paste Values. Here we learn to paste values in Excel VBA with & without formats, from another worksheet, unique values, examples. You can learn more from the following articles –

• Copy Paste in VBA
• VBA FILECOPY
• VBA UsedRange