Replace the paste function with past special value

[sheridan1234]

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.

 

[Al Chara]

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."

 

[EvR]

Or take a look here: http://www.jkp-ads.com/articles/catchpaste.asp

 

[L33]

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!)

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

 

[sheridan1234]

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!)

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]

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