Pasting Only values - VBA Code Issue

[DaRTH KiRo]

I am using the VBA code below to only allow values, no formatting, to be pasted in my spreadsheet. However, once I copy data it pastes it anywhere I click and won't stop. This may cause problems for us if someone who does not know about it pastes data and clicks off into another cell. Can someone please assist me with changing the code so that it will only do it once, or always someone to ctrl+v and paste only values?

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
Target.PasteSpecial xlPasteValues
Application.CutCopyMode = True
End Sub

 

[Fluff]

Try

Application.CutCopyMode = False

 

[DaRTH KiRo]

It does not appear that that changed anything. I realized something else though, after pasting from another excel sheet the cells that were copied are no longer copied. So the only real issue is if I have something copied from a source outside of excel. If I copy something from an email it will paste it where I click in excel forever, even after closing and re-opening, it also does not let me undo.

 

[Fluff]

What are you trying to do?

 

[DaRTH KiRo]

I am trying to only allow values to be pasted in excel. We are pulling data from other sheets that have different formatting into one sheet that needs to have specific formatting. I do not want the other sheets formatting to be pulled.

 

[Fluff]

How about

Sub PasteValuesOnly()
Selection.PasteSpecial xlPasteValues
End Sub

& then you can assign Ctrl V as a keyboard shortcut

 

[DaRTH KiRo]

Would the shortcut need to be added to everyone's computer, or is it saved within the specific excel file?

 

[Fluff]

It would need to be set on each computer