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.

VBA Paste Values - Definition Example - 2

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.

VBA Paste Values - Definition Example - 3
Key Takeaways
  • The primary goal of using VBA PasteValues is to copy data from one range and paste only the values (excluding formulas, formatting, etc.) into another range.
  • Identify the source and target ranges in your code and use the Copy method to copy data from the source range.
  • Utilize the PasteSpecial method with the xlPasteValues parameter to paste only the values in the target range.
  • Clear the clipboard after pasting values using Application.CutCopyMode = False to prevent interference with subsequent copy-paste operations.

How to Paste Values in Excel using VBA?

Follow the steps to see how to use VBA Paste Values and Formats.

  1. Click “Developer” and then go to “Visual Basic” in a given Excel WorkBook.

    How to Paste Values in Excel using VBA - Step 1a

  2. In the VBA Editor, select “Module.”

    How to Paste Values in Excel using VBA - Step 2a

    It will open a new module in the workbook where you can start coding in Excel VBA.
    Consider a table with values as shown below.

    Notice the table. It has various changes, such as the values being in bold, the font color changing, and the cell background color is changed. You can copy and paste the values along with the formats.
    You can see the many ways of using VBA Paste Values only and no formatting and also Values and Formats.

  3. Start with defining the subroutine you want to perform VBA Paste Values on.

    How to Paste Values in Excel using VBA - Step 3

  4. Copy the table range given above.

    How to Paste Values in Excel using VBA - Step 4

  5. First, use Paste Values and Formats using the option “xlPasteAll.”

    How to Paste Values in Excel using VBA - Step 5

  6. Similarly, perform VBA Paste Values only with the option “xlPasteValues.”

    How to Paste Values in Excel using VBA - Step 6

  7. Remove the copied values from the Excel clipboard using the code below.

    How to Paste Values in Excel using VBA - Step 7


    Sub CopyAndPasteRanges()
    Range(“D1”).PasteSpecial xlPasteAll
    Range(“E1”).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    End Sub

  8. Use either “F5” or the run button on the top to run the above subroutine. Go back to the worksheet and view the output below.

    How to Paste Values in Excel using VBA - Step 8a

    The values are pasted with formats using VBA Paste Values and formats.

    Similarly, the values are pasted using VBA Paste Values only, no formatting. Now that you’ve seen a simple example of how to use Paste Values, you can learn more by viewing more examples of different scenarios where VBA Paste Values are used.

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.


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.

VBA Paste Values in Excel - Example 1
  • Step 1: Initialize a subroutine to copy and paste values with the PasteSpecial function.
 Example 1 - Step 1
  • Step 2: Copy the cell C6.
Example 1 - Step 2
  • Step 3: Select the destination cell. In this case, D6 cell becomes
Example 1 - Step 3

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”.
Example 1 - Step 4
  • Step 5: Paste only the format in cell “B6”, where a value has already been declared.
Example 1 - Step 5


Sub CopyWithPasteSpecial()
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.
VBA Paste Values in Excel - Example 1 - Step 6

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.

VBA Paste Values in Excel - Example 2

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

Example 2-1

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.
Example 2 - Step 1
  • Step 2: Find the size of the table and return the number of rows.
Example 2 - Step 2

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.
Example 2 - Step 3
  • Step 4: Along the loop, copy the values from the 2nd column and paste them in the 7th column using the VBA Cells function.
Example 2 - Step 4a

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

Example 2 - Step 4b

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

Example 2 - Step 4c

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.
Example 2 - Step 5
  • 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.
 Example 2 - Step 6


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.
VBA Paste Values in Excel - Example 2 - Step 7

Example #3 – Copy from One Worksheet to Another

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

Excel VBA Paste Values - Example 3

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.
Example 3 - Step 1
  • Step 2: Set the given table as a ListObject variable.
Example 3 - Step 2
  • Step 3: Define the target sheet and cell value where you will paste the copied table.
Example 3 - Step 3
  • Step 4: Copy the table.
Example 3 - Step 4

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

  • Step 5: Perform the paste values only into the target range.
Example 3 - Step 5
  • Step 6: To perform VBA Paste Values and Formats as a whole, don’t add any PasteSpecial Method.
Example 3 - Step 6


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”)
targetRange.PasteSpecial xlPasteValues
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:

Excel VBA Paste Values - Example 3 - Step 7a

Paste Values and Formats:

Excel VBA Paste Values - Example 3 - Step 7b

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)

1. How to copy and paste unique values in Excel VBA?

Consider this set of values.

Excel VBA Paste Values - FAQ 1

To copy the unique values,

• Copy unique values: Range("D1:D10").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("E1"), Unique:=True

• Paste unique values: Range("E1:E10").Copy Destination:=Range("F1")

• Clear clipboard: Application.CutCopyMode = False

FAQ 1 - Step 1

The output is,

Excel VBA Paste Values - FAQ 1 - Step 2

2. How to paste values in merged cells VBA?

Consider the merged cell value:

Excel VBA Paste Values - FAQ 2

Using the code below, you can perform VBA Paste Values Only for merged cells.

FAQ 2 - Step 1

This results in the output as shown below:

Excel VBA Paste Values - FAQ 2 - Step 2

3. Are there any variations of Paste Values in VBA?

Yes, there are several variations of VBA Paste Values available in Excel.
• VBA Paste Values Only:
Range("A1:A10").PasteSpecial Paste:=xlPasteValues
• VBA Paste Values and Number Formats:
Range("B1:B10").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
• VBA Paste Values and Transpose:
Range("C1:C10").PasteSpecial Paste:=xlPasteValues,Transpose:=True
• VBA Paste Formulas:
Range("D1:D10").PasteSpecial Paste:=xlPasteFormulas
• VBA Paste Formulas and Number Formats:
Range("E1:E10").PasteSpecial  Paste:=xlPasteFormulasAndNumberFormats

4. How to add a button to my Excel toolbar for VBA Paste Values?

Open Excel.
Access the Developer Tab.
Open the VBA Editor:
Insert Module:
Select Insert → Module.
Paste your VBA Code into the module.
Create Macro Button:
Go to the “Developer” tab, click “Insert,” then choose “Button (Form Control).”
Draw a button on your sheet.
• Assign your macro to the button.
Assign your VBA macro to the button.
• Test Button:
Click the button to execute your VBA Paste Values macro.

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.

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 –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *