Replace the paste function with past special value

sheridan1234

New Member
Joined
Aug 9, 2012
Messages
3
I created a spreadsheet many years ago for my work place to assist with generating weekly paperwork. All the information in the spreadsheet is used in a mail merge to generate the weekly paperwork.

My issue is this, starting last year we got a few stubborn new employees that refuse to use paste special on their own when copying information into the spreadsheet. As a result, when they paste data into the spreadsheet it erases all my underlying formulas and conditional formatting.

I need a way to disable the paste function and replace it so that anytime someone tries to paste something into the spreadsheet (by key command or mouse) it will paste special the values only and leave all my other formatting intact.

I've tried searching around on this site but I sill can't find an example where I set this up for the entire spreadsheet.

Thanks for any help,
Shane.
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Al Chara

MrExcel MVP
Joined
Feb 21, 2002
Messages
1,701
Take a look at this thread, which is similar to your question. The recommendation was to "disable normal pastes then use a Custom Menu that is attached to Recorded Macro that ONLY paste Values."
 

L33

Board Regular
Joined
Jul 2, 2008
Messages
104
I had the same issue recently and this solution fit my purposes best: http://www.mrexcel.com/forum/showth...Special-Values&p=263183&viewfull=1#post263183

*EDIT
Basically, you need to right-click on the sheet and select View Code, then paste this code in to the VB editor and save. (Edited this post as unsure of your knowledge level, so thought I'd better clarify!)

Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)''http://www.mrexcel.com/forum/showthread.php?56674-Force-Paste-Special-Values&p=263183&viewfull=1#post263183
''Forces Paste Values instead of paste to maintain formatting


If Application.CutCopyMode = xlCopy Then


Application.EnableEvents = False


Application.Undo
Target.PasteSpecial Paste:=xlPasteValues


Application.EnableEvents = True


End If


End Sub
 
Last edited:

sheridan1234

New Member
Joined
Aug 9, 2012
Messages
3
I had the same issue recently and this solution fit my purposes best: Force Paste Special Values

*EDIT
Basically, you need to right-click on the sheet and select View Code, then paste this code in to the VB editor and save. (Edited this post as unsure of your knowledge level, so thought I'd better clarify!)

Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)''http://www.mrexcel.com/forum/showthread.php?56674-Force-Paste-Special-Values&p=263183&viewfull=1#post263183
''Forces Paste Values instead of paste to maintain formatting


If Application.CutCopyMode = xlCopy Then


Application.EnableEvents = False


Application.Undo
Target.PasteSpecial Paste:=xlPasteValues


Application.EnableEvents = True


End If


End Sub

I have tried and tried to use the codes that I have found but none seem to work.

Perhaps I am not inputting these codes correctly.

Could someone please take the time to walk me through step by step on how to input this stuff (give me directions as if I were the stupidest person on the planet please)

Thanks
 

sheridan1234

New Member
Joined
Aug 9, 2012
Messages
3
I think I know what some of the issues with the provided codes are now. They work fine as far as workbook to workbook pasting goes but if you copy some information from a web page, the code does not enforce the paste value function. Does anyone have any suggestions about how to rework the code to get it work when pasting from a webpage?

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,118,866
Messages
5,574,724
Members
412,616
Latest member
schabo
Top