You can use the following syntax in VBA to copy a range of cells and paste the values along with the source formatting to a new location:
Sub PasteWithFormatting()
Range("A1:C11").Copy
Range("A13").PasteSpecial Paste:=xlPasteAllUsingSourceTheme
Application.CutCopyMode = False
End Sub
This particular macro will copy the cells in the range A1:C11 and paste the values from the cells with their formatting into the range starting at cell A13.
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 and Keep 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:C11 to a new location and keep the formatting.
We can create the following macro to do so:
Sub PasteWithFormatting()
Range("A1:C11").Copy
Range("A13").PasteSpecial Paste:=xlPasteAllUsingSourceTheme
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 with their formatting into the cell range starting at cell A13.
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 Paste Values Only with No Formatting
VBA: How to Copy Visible Rows to Another Sheet
VBA: Copy Rows to Another Sheet Based on Criteria
