VBA: How to Paste Values Only with No Formatting


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 here.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

VBA: How to Find Last Used Row
VBA: How to Count Number of Rows in Range
VBA: How to Count Number of Used Columns

Leave a Reply

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