Copy Paste Special

[sj1601]

Hi All,

Is there keyboard shortcut for pasting values? I have to repeatedly perform this task and would save me a lot of time if I simply did ctrl+c and then the keyboard s/cut for paste value.

Thanks.

sj1601

 

[Zack Barresse]

Yes, use Cbrine's steps 6 - 8. Where I commented in my code (the green text) is where you need to change that text to the correct module name of wherever you paste this code. Putting this in your Personal.xls file will allow you access to it the entire time you have Excel open. You'll need to do one more thing if this is what you want:

1) Open the VBE (Alt + F11)
2) Select PERSONAL.XLS on the left, expand all folders
3) Double click the ThisWorkbook module, paste this code...



<font face=Tahoma New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_BeforeClose(Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)
    <SPAN style="color:#00007F">Call</SPAN> ResetRightClick
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Open()
    <SPAN style="color:#00007F">Call</SPAN> AppendRightClick
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>



Once that is in place, you can close the VBE. To answer your questions:

1. Is there a way to undo the whole activity? Just in case I screw up, I must know how to get out of it and restore status quo.
There is currently no way to do this. When you run code, it deletes the undo stack. If you want a surefire way to do this, you must code it so, which would be kind of a pain. We can look into this if you'd like.

2. Will this feature work on every spreadsheet I work on(old and new)?
If you put it in your PERSONAL.XLS file, yes (as directed above).

3. I work on lots of spreadsheets with macros built on them (I didn't write those). Will this code ever cause any "conflicts" with those macros?
No, this does not act on any other macros. The only way that would happen is if you had a cell change event, in which case we could add some lines of code to inhibit that.

4. Is this the same code that firefytr mentioned?
I think so.

 

[sj1601]

Thanks firefytr for your quick responses.

A couple of clarifications:

1. Regarding the undo question I asked, I meant if there is a way to cancel the whole code/macros setup once I install it? That would bring my EXCEL back to where it was. If yes, could you please tell me how to do it?
2. Regarding the "cell change event" that you mentioned above (3), I do not particularly understand what you mean. Could you please elucidate?

Thanks.

 

[Zack Barresse]

No problem.

Answers:
1) This code only adds two items to the cells right click menu. To reset, you can either take out the ThisWorkbook code I posted for you (this will ensure it doesn't fire when you open Excel) or you can run the ResetRightClick routine anytime to reset the cell right click menu.

2) If you don't know whether you do or not, chances are you don't. If you inhereted this workbook, there is a slight chance of this. To check, open your VBE and open all of the worksheet modules and see if there is any code in there. If not, you have nothing to worry about. To adapt the code I posted to take care of this, append the following code ...



<font face=Tahoma New>    <SPAN style="color:#007F00">'Leave values</SPAN>
    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Selection
        c.PasteSpecial xlPasteValues
    <SPAN style="color:#00007F">Next</SPAN> c
    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN></FONT>


... and ...


<font face=Tahoma New>    <SPAN style="color:#007F00">'Leave values</SPAN>
    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Selection
        c.Value = c.Value
    <SPAN style="color:#00007F">Next</SPAN> c
    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN></FONT>



HTH

 

[sj1601]

Great. You guys are geniuses!

I will first perform all this coding on my personal computer before my work PC, just to be sure.

Thanks a lot. I will let you know how it goes.

sj1601

 

[Cbrine]

Zack,
Thanks for the code. I didn't remember that the right-click menu was just a commandbar("cell"), till you posted the code. Added it to my archive.

Cal

PS-Sorry I was out of the office yesterday, so I was not able to answer any questions.

 

[Zack Barresse]

I really like all the right-click command bars. My favorites are:

Name:
"Cell"
Location:
Right click on any cell.

Name:
"Ply"
Location:
Right click on any sheet tab.

Name:
"Row"
Location:
Right click on any row header.

Name:
"Column"
Location:
Right click on any column header.

Name:
"Document"
Location:
Right click on Excel icon directly left of File | Edit | View...

Name:
"ActiveX Control"
Location:
Right click on any ActiveX control.

Name:
"OLE Object"
Location:
Right click on any shape/OLE Object.

Find a full list of controls and their IDs here:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q159466 (97)
http://support.microsoft.com/kb/213552/en-us (2000)

 

[Cbrine]

Thanks Zack,
WOW, I knew it was possible to modify the right click menu, but I didn't realize it was that comprehensive. Adding this post and the links to my archive spreadsheets.

Cal

 

[tvkrys]

Since the paste special functions shortcuts are more than a couple of keystrokes, it may be easier to place buttons on a toolbar that initiate paste special macros. If you right click on a toolbar then go to Commands | Edit, you'll find premade versions for Values and Formatting. I also paste formulas a lot so I created macro and assigned it to a button. I then edited button images to distinguish them from each other.

You can create a macro for any string of shortcut keys using the SendKeys command. For example, my Paste Special Formula looks like this:

SendKeys "%esf~"

%e = {Alt+e} which selects the edit menu
s selects the paste special button
f selects formula
~ = {Enter}

You can revise this as needed to chose other elements in the paste special family OR to create a macro for any keystroke combination. Check out "SendKeys" in Help from the VBA Editor. (My style is a little different, but works when waits not needed.) Help in Excel can yield a list of keyboard shortcuts or follow the underscores ("_") on the menus to determine the keys on your own.

Krys

 

[Zack Barresse]

Hi krys,

Your first suggestion is a great one that hasn't been mentioned. Adding the (native) buttons to a toolbar is an excellent method. I highly disagree about SendKeys though. They are very unstable IMHO, I stay waaay clear of them.

 

[PolarBear]

Wow, these guys write industrial strength code! I had the same problem, and just wrote the following:

Sub SpecialPaste()
'
' SpecialPaste Macro
' Macro recorded 9/9/2005 by MYRONCA
'
' Keyboard Shortcut: Ctrl+m
'
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
End Sub

I assigned it to Ctrl-M, and now copy/paste is just ctrl-c, move to destination, ctrl-m. I've never had a problem with it not working, though as others pointed out, you can't undo the paste operation.

Hope this helps...