Billdub417
New Member
- Joined
- Nov 5, 2019
- Messages
- 45
Hello,
I use the following code so that when people paste info into this workbook, it only pastes values.
However, this doesn't work when pasting from 1 workbook to another (as paste values is not an option)
Is there a way to still get it to paste the data in?
thanks,
I use the following code so that when people paste info into this workbook, it only pastes values.
However, this doesn't work when pasting from 1 workbook to another (as paste values is not an option)
Is there a way to still get it to paste the data in?
thanks,
VBA Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim UndoString As String, srce As Range
On Error GoTo err_handler
UndoString = Application.CommandBars("Standard").Controls("&Undo").List(1)
If Left(UndoString, 5) <> "Paste" And UndoString <> "Auto Fill" Then
Exit Sub
End If
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Undo
If UndoString = "Auto Fill" Then
Set srce = Selection
srce.Copy
Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.SendKeys "{ESC}"
Union(Target, srce).Select
Else
Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
err_handler:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub