How to Copy Only Values and Formatting from a Range to Another in Excel

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.

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.

copy only values and format 1

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.

copy only values and format2

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

 

copy only values and format3

 

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.

Get the position of the nth using excel vba1
Step2: then the “Visual Basic Editor” window will appear.

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

export each sheet to csv2

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

copy only values and format4

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.

copy only values and format5

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

copy only values and format6

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

copy only values and format7

Step8: Let’s see the result:

copy only values and format8

You might also like:

Comments

So empty here ... leave a comment!

Leave a Reply

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

Sidebar