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:
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
