How to restrict to paste values only (prevent formatting) in Excel?
Normally, we paste copied data with just pressing the Ctrl + V keys simultaneously. And that will paste the copied values along with all the cell formatting. If you just need to paste the values only and restrict the cell formatting, the following methods can help you.
Restrict to paste values only (prevent formatting) with paste values feature
An easy way to restrict to paste values only is pasting the copied data as values only in Excel.
1. After copying data, right click the destination cell you will paste the data into.
2. Then click the Values button under the Paste Options section in the right-clicking menu. See screenshot:
You can see only values of copied cells are pasted into the destination cells.
Restrict to paste values only (prevent formatting) with VBA code
The following VBA code helps you paste only values of copied cells, and restrict all cell formatting. Please do as follows.
1. Press Alt + F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.
2. In the Microsoft Visual Basic for Applications window, double click the ThisWorkbook in the Project right pane to open the ThisWorkbook code window. Then copy and paste the below VBA code into the window.
VBA code: Restrict to paste values only in Excel
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
Application.CutCopyMode = True
3. Press the Alt + Q keys simultaneously to close the Microsoft Visual Basic for Applications window.
Now, copy your data, and go to the destination worksheet. Only one click or right click on the destination cell will paste the values of copied cells without any formatting immediately.
Restrict to paste values only with Kutools for Excel
Kutools for Excel’s Copy Ranges utility helps you to easily paste only values of a selected range or multiple selected ranges in Excel. Please do as follows.
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More than 300 powerful features. Supports Office/Excel
2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features
30-day free trial. 60-day money back guarantee.
Hi! for Kutools 'Copy Ranges', I realized there is limit on number of columns that can be copy at a time. I am working with 7,500 columns. I select 7,500 columns as the range, but it can only copy and paste partial of what I had selected, not all. I am planning to copy all these columns in one go and paste. Is there any way I can do that ? just to speed things up. Any help would highly be appreciated. Thanks!
Hi, is there code for working with ctrl+v. This code paste automatically when selecting cell. I found module code below link but couldn't find code for workbook like this.
This code was great, but it was pasting items in the system clipboard that were copied from other programs. I put it in an 'If' statement which does nothing, unless Excel cells are actually in Cut or Copy mode. i.e. moving border around the cell(s).
Application.CutCopyMode has three modes: False = Not in Cut or Copy mode. | xlCopy = In Copy mode. | xlCut = In Cut mode.
'If in Cut or Copy mode, Paste Values Only
'If Not in Cut or Copy mode, skip and do nothing.
On Error Resume Next
If Not Application.CutCopyMode = False Then Target.PasteSpecial xlPasteValues
Application.CutCopyMode = True End If
This was a bit of a bugger for me to figure out. Hope it helps someone else.
-Travis (IT Professional since 1996)
Hi Travis, is there code for working with ctrl+v. This code paste automatically when selecting cell. I found module code below link but couldn't find code for workbook like this. I hope what I have requested is possible.