This Guy - Paste Special Macro WIP. Determining if cut or copy was called...

[This_Guy]

Hi,

I like to maximize efficiency and go really fast when I work with data. I'm pretty good at doing this too, but Excel 2010's keyboard shortcuts to retrieve the paste special values menu prompt is slowing me down. I access this dialog/prompt all the time and I am annoyed at how Microsoft just up and changed the keyboard shortcut's combination code. It's not a big change, but it does disrupt my workflow from time to time.

This lead me to write a macro to execute the new keyboard shortcut myself...

Sub test()
Application.SendKeys ("+{F10}ss")
End if

To make this this macro work, one must select a cell, copy its contents (or press Ctrl+C), and then call the macro. It basically just sends the Shift+F10 keyboard combination to Excel and then presses "s" twice in a row.

The macro works perfectly when the previous command is copy. As many of you know, you can not paste special one or more cells that were just cut. Instead, they must be copied and then they can be paste special'd.

As a result, I am hoping to learn of an approach in VBA, to determine if the initial operation was a cut or a copy, and then if it was a copy, then run this code otherwise just paste. Accordingly, I am planning to bind this macro to the "Ctrl+V" keyboard combination. In the end, I would like any cut operations to seamlessly execute while the copy operations bring up the paste special menu every time.

Any ideas?

Thanks,
This guy

 

[JLGWhiz]

I didn't try this, but seems like it should work.

If Application.CutCopyMode = xlCopy Then
    Application.SendKeys ("+{F10}ss")
End If

 

[This_Guy]

You sir, just blew my mind. My life is now complete. Thank you!

If you're a keyboard junkie like myself, then you'll probably understand the gravity of this moment. I can't believe this is actually possible.

For those of you who would appreciate the macro as well, I have reproduced it below.

Sub Paste_Special()
Application.ScreenUpdating = False

If Application.CutCopyMode = xlCopy Then
    Application.SendKeys ("+{F10}ss")
Else
    ActiveSheet.Paste
End If

Application.ScreenUpdating = True
End Sub

Thanks again JLGWhiz

 

[JLGWhiz]

You're welcome,
Regards, JLG

 

[Chris Macro]

Sub Paste_Special()
Application.ScreenUpdating = False

If Application.CutCopyMode = xlCopy Then
    Application.SendKeys ("+{F10}ss")
Else
    ActiveSheet.Paste
End If

Application.ScreenUpdating = True
End Sub

I don't understand why you would still try to Paste if there was nothing copied on the clipboard. Wouldn't this give you an errror? I'm thinking you might want something like this:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> Paste_Special()<br>Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br><br><SPAN style="color:#00007F">If</SPAN> Application.CutCopyMode = xlCopy <SPAN style="color:#00007F">Then</SPAN><br>    Application.SendKeys ("+{F10}ss")<br>    CutCopyMode = <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">Else</SPAN><br>    MsgBox "Nothing is currently copied"<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br>Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[This_Guy]

Hey Chris,

That is an awesome question. Thank you for inquiring about this, as it is something I probably should have mentioned earlier.

As you know, I had structured the macro to catch the copy operations so that it would then call the paste special dialog. Otherwise, the macro makes the assumption that we are in the midst of a cut operation and so it pastes whatever is on the clipboard.

However, some more reasonable code might look something like this:

If Application.CutCopyMode = xlCopy Then
    Application.SendKeys ("+{F10}ss") 'Call the paste special dialog
Elseif Application.CutCopyMode = xlCut Then
    ActiveSheet.Paste
Else 
    MsgBox "Goodbye! (Nothing to paste)"
End if

In doing so, we would prevent any error messages that would arise, should we call the macro and actually not in be the midst of a copy nor a cut operation. All is well and good at this point, except if we copy or cut some content from an external program. Oh the pain this causes, because Excel's CutCopyMode will = False, even though there is content on the clipboard to be pasted.

My solution was to bypass this little obstacle all together. So I didn't include the Elseif statement. Occasionally, I do encounter the error message when CutCopyMode = False and there is nothing on the clipboard to be pasted. Although it doesn't happen enough for me to include the On Error Resume Next line of code. I try to avoid that command wherever possible.

I should have mentioned something about all of this earlier, so I really appreciate your question. Looking back, it was a little bit reckless on my part to post code without providing adequate background.

Thanks again,
This Guy

 

[Chris Macro]

Hey no worries This Guy, just wanted to make sure you knew what you were doing! Glad you were able to get your question answered. Cheers!