Copy values-formatting from a range to another (Excel)


To totally unlock this section you need to Log-in


Login

In Excel, copy values and formatting only can do as follows:

  1. Select the range you want to copy.
  2. Click Edit > Copy, or right click and select Copy.
  3. Select a cell or range you want to paste the values and formatting.
  4. Choose Edit, Paste Special or right click and choose Paste Special, and click Values and source formatting, see the following screenshot:

Copy values-formatting from a range to another (Excel)

Then only the values and cell formatting are pasted in the selected cell.

Copy values and all formatting with VBA

Running VBA can quickly copy values and formatting:

Hold ALT button and press F11 on the keyboard to open a Microsoft Visual Basic for Application window. Click Insert > Module, and copy the VBA into the module:

Sub CopyValuesAndNumberFormats()
'Update 20130815
Dim CopyRng As Range, PasteRng As Range
xTitleId = "Copy Values & Formatting"
Set CopyRng = Application.Selection
Set CopyRng = Application.InputBox("Ranges to be copied :", xTitleId, CopyRng.Address, Type:=8)
Set PasteRng = Application.InputBox("Paste to (single cell):", xTitleId, Type:=8)
CopyRng.Copy
PasteRng.Parent.Activate
PasteRng.PasteSpecial xlPasteValuesAndNumberFormats
PasteRng.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End Sub

Click Run button or press F5 to run the VBA. And there is a dialog displayed on the screen, and you should select the range cells that you want to copy the values and formatting. See screenshot:

Copy values-formatting from a range to another (Excel)

Click Ok and another dialog is displayed for you to select a cell to paste the result. See screenshot:

Copy values-formatting from a range to another (Excel)

Click Ok, then the cells' values and formatting are pasted in the selected cell. See screenshot:

Copy values-formatting from a range to another (Excel)

Note: With this VBA code, you can also paste the values and formatting into other worksheets that you want.