Hi All:
The code below is ALMOST doing what I want but there is one problem with it. Due to my lack of VBA skills I am hoping that someone can tell me what needs to be added or subtracted from the code.
The code changes a Copy/Paste to a Copy/PasteSpecial/Values without the user being made aware of it. This is only suppose to happen on my sheet titled "Meeting Minutes".
Problem: When I Paste onto the sheet "Meeting Minutes" it seems to be working. When I Paste onto any other sheet it works but it pops up another menu when the sheet is not titled Meeting Minutes. The menu is the Paste Menu and it is just asking how I want to Paste.
Can anybody tell me how to get rid of that piece and just have the code do a standard Paste? Hope this makes sense... Here is the code:
Can anyone assist in getting this to work WITHOUT popping up the Paste Menu?
THANKS,
Mark
The code below is ALMOST doing what I want but there is one problem with it. Due to my lack of VBA skills I am hoping that someone can tell me what needs to be added or subtracted from the code.
The code changes a Copy/Paste to a Copy/PasteSpecial/Values without the user being made aware of it. This is only suppose to happen on my sheet titled "Meeting Minutes".
Problem: When I Paste onto the sheet "Meeting Minutes" it seems to be working. When I Paste onto any other sheet it works but it pops up another menu when the sheet is not titled Meeting Minutes. The menu is the Paste Menu and it is just asking how I want to Paste.
Can anybody tell me how to get rid of that piece and just have the code do a standard Paste? Hope this makes sense... Here is the code:
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
' this code will undo PASTE and instead do a PASTE SPECIAL VALUES which will
' allow you to retain FORMATS in all of the cells in all of the sheets, but
' will also allow the user to COPY and PASTE data
Dim UndoString As String
Dim srce As Range
On Error GoTo err_handler
If Target.Parent.Name <> "Meeting Minutes" Then
Application.CommandBars("Standard").Controls("&Paste").Execute
Else
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
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
err_handler:
Application.ScreenUpdating = True
Application.EnableEvents = True
End SubCan anyone assist in getting this to work WITHOUT popping up the Paste Menu?
THANKS,
Mark
