Paste Values Only with No Formatting in VBA

By /

Paste Values Only with No Formatting in VBA is an option that allows you to paste the values of the copied cells without formatting the pasted cells. This is useful when you don’t want the formatting to be copied along with the values. It can also be used when you want to paste the values of the copied cells into a certain location without having to reformat the cells.

You can use the following syntax in VBA to copy a specific range of cells and paste the values only to a new location with no formatting:

Sub PasteNoFormatting()

Range("A1:D9").Copy
Range("A12").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

End Sub

This particular macro will copy the cells in the range A1:D9 and paste the values from the cells without any formatting into the range starting at cell A12.

Note: The line Application.CutCopyMode = False specifies that the cut and copy mode should be turned off after running the macro.

The following example shows how to use this syntax in practice.

Example: Paste Values Only with No Formatting Using VBA

Suppose we have the following dataset in Excel that contains information about various basketball players:

Suppose we would like to copy and paste all of the values in the range A1:D9 to a new location without the formatting.

We can create the following macro to do so:

Sub PasteNoFormatting()

Range("A1:D9").Copy
Range("A12").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

End Sub

When we run this macro, we receive the following output:

VBA paste values only no formatting

Notice that the values from the original cells have been pasted into a new location without any formatting.

Note: You can find the complete documentation for the VBA PasteSpecial method .

x

Related terms:

  1. What is the difference between cat() and paste() in R?
  2. How do I apply conditional formatting to cells using VBA?
  3. Excel: Apply Conditional Formatting to Yes/No Values
  4. Excel: Conditional Formatting Based on Even and Odd Values
  5. Student asks: How do I apply conditional formatting to duplicate values?
  6. How to Apply Conditional Formatting if Between Two Values
  7. Delete Files Using VBA (With Examples) How to use VBA to delete files
  8. How do I convert a string to a long number in VBA?
  9. I would like to insert a row with formatting.
  10. Can I do conditional formatting if another cell is not empty?