This post will guide you how to copy values and formatting from a given range to another range in Excel. How do I copy only values and formatting with VBA Macro in Excel 2013/2016.

• Copy Only Values and Formatting with Paste Special
• Copy Only Values and Formatting with VBA

Assuming that you want to copy a selected range of cells to another range, and you only want to keep the values and formatting in the selected range of cells. You can use the Paste Special feature or use a new VBA Macro to accomplish the result.

Copy Only Values and Formatting with Paste Special

---

To copy only values and formatting from a range to another in Excel, you can do the following steps:

Step1: select one range that you want to copy, then press Ctrl + C keys on your keyboard to copy the selected cells.

Step2: select one blank cell that you want to place values, such as: Cell E1. And right click on it, choose Paste Special from the context menu, and then click Values and source formatting.

Step3: you would see that all values and formatting has been copied to another range.

 

 

Copy Only Values and Formatting with VBA

---

You can also use an Excel VBA Macro to copy only values and formatting from a selected range of cells to anther cells. Just do the following steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.

Step4: paste the below VBA code  into the code window. Then clicking “Save” button.

Sub CopyOnlyValuesAndFormat()
    Set selectedRange = Application.Selection
    Set selectedRange = Application.InputBox("Select one range that you want to copy :", "CopyOnlyValuesAndFormat", selectedRange.Address, Type:=8)
    Set dRange = Application.InputBox("Select one blank Cell to palce values:", "CopyOnlyValuesAndFormat", Type:=8)
    selectedRange.Copy
    dRange.Parent.Activate
    dRange.PasteSpecial xlPasteValuesAndNumberFormats
    dRange.PasteSpecial xlPasteFormats
    Application.CutCopyMode = False
End Sub

Step5: back to the current worksheet, then run the above excel macro. Click Run button.

Step6: Select one range that you want to copy . Click OK button.

Step7: Select one blank Cell for placing the values.  Click OK button.

Step8: Let’s see the result: