Hi Friends,
I am trying to use below VBA code to force paste special operation when a user performs cut/copy & paste.
I am having 2 issues here:
1. The sheet change event triggers only the first-time (after opening the workbook) and thereafter it does not.
2. When (the first time) the code is triggered, it does not work correctly, it simply writes "suf" in the target cell, which is a later part of paste-special short-cut key (using sendkeys in my below code).
Please Note: To avoid the 2nd issue, I added half-second pause between sendkeys, but it does not work
Following Declaration is in code module:
and below VBA code goes in Sheet Module:
FYI, earlier, I tried to use above code on a different workbook but always ended up in a macro loop, may be it had something to do with sheet change event being triggered again and again. That workbook didn't have any other event macros, I still have no clue why it behaves differently on that file.
Someone please tell me what is wrong with the code. Please.
Thanks.
I am trying to use below VBA code to force paste special operation when a user performs cut/copy & paste.
I am having 2 issues here:
1. The sheet change event triggers only the first-time (after opening the workbook) and thereafter it does not.
2. When (the first time) the code is triggered, it does not work correctly, it simply writes "suf" in the target cell, which is a later part of paste-special short-cut key (using sendkeys in my below code).
Please Note: To avoid the 2nd issue, I added half-second pause between sendkeys, but it does not work
Following Declaration is in code module:
Code:
#If VBA7 Then ' Excel 2010 or later
Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal Milliseconds As LongPtr)
#Else ' Excel 2007 or earlier
Public Declare Sub Sleep Lib "kernel32" (ByVal Milliseconds As Long)
#End Ifand below VBA code goes in Sheet Module:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
'Get the last undo action performed by the user
UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1)
'If last action was not a paste, skip the operation
If Left(UndoList, 5) <> "Paste" Then GoTo SkipPasteSpecial
'This method of PasteSpecial keeps the undo buffer
Application.SendKeys "^z", True 'Undo paste performed by the user
Sleep 1000
Application.SendKeys "%e", True 'Not using CTRL+SHIFT+V (some macros use it for customized operations)
Sleep 1000
Application.SendKeys "s", True 'Loads pastespecial dialog box
Sleep 1000
Application.SendKeys "u", True 'Read Trick1 below
Sleep 1000
Application.SendKeys "f", True 'Read Trick2 below 'Change "f" to "v" to paste as value instead of formula.
Sleep 1000
Application.SendKeys "{ENTER}", True 'Apply paste special
Sleep 1000
'Trick1: if copy source is external, select "Unicode Text" format (use t for "Text" format)
'Trick2: Changes selection to formula/values, if copy source is excel. If not this keypress changes nothing
SkipPasteSpecial:
Application.EnableEvents = True
End SubFYI, earlier, I tried to use above code on a different workbook but always ended up in a macro loop, may be it had something to do with sheet change event being triggered again and again. That workbook didn't have any other event macros, I still have no clue why it behaves differently on that file.
Someone please tell me what is wrong with the code. Please.
Thanks.
