Paste Values only

[trevford1]

Hello, I have a little challenge for somebody:

I have a workbook with 2 sheets of data, which an entire department of people use. It's a common and accepted practice to copy/ paste values around the sheets. The problem is that the formatting gets copied too, which makes a mess after a while.

On both sheets (entire workbook) I would like to be able to paste values only. This is easy enough, but I would like this to work for all of the following:

1. Mouse right-click paste
2. CTRL V
3. (menu) Edit>Paste
4. Anything else I've missed.

In other words, it needs to work within the parameters that the users already know, rather than creating another macro button to do this task. The job and sheet are already fairly complicated, so the last thing people need is another button to use, especially since another button will slow down the process.

In the opposite end, it could be designed to copy values only. Whatever works and is fail-safe for the basic excel user.

What else also works, is if the user double-clicks the cell before they paste. Then only the values are pasted. Just something to consider.

I really don't know hardly anything about all this, so please be very specific, including if I have to install in a module or something. Thanks very much.

 

[jbeaucaire]

See if this page's solution takes care of your need:

http://www.mrexcel.com/archive/VBA/4522.html

 

[trevford1]

Thanks for the response. I've been busy and had to put off this part of the code for now.

 

[Devin]

I recently watch Mr. Excel podcast # 958 and he shows a way to use ctrl+shift+v to paste values only. It's is a great way to complement the existing shortcuts without confusing anyone.

Here is my macro setup:

In ThisWorkbook:

Private Sub Workbook_Open()

​

Application.OnKey Key:="^+v", Procedure:="PasteValues"

​

Application.OnKey Key:="^+c", Procedure:="Convert"
End Sub

In Module1:

Sub PasteValues()

​

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub
Sub Convert()

​

Selection.Value = Selection.Value
End Sub

 

[Stormseed]

I recently watch Mr. Excel podcast # 958 and he shows a way to use ctrl+shift+v to paste values only. It's is a great way to complement the existing shortcuts without confusing anyone.

Here is my macro setup:

In ThisWorkbook:

Private Sub Workbook_Open()
Application.OnKey Key:="^+v", Procedure:="PasteValues"
Application.OnKey Key:="^+c", Procedure:="Convert"
End Sub

In Module1:

Sub PasteValues()
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub
Sub Convert()
Selection.Value = Selection.Value
End Sub

Incase I understand what you are trying to say here:

You might want to know that you can use any combination of keys to paste values according to your WILL

Create a "Paste Values" button on the toolbar by using the Customize option in Tools menu - right click the button - in the "Name" field, shuffle the ampersand (&) to any character on the keyboard that you want to press with a combination of "ALT" and there you go !

 

[trevford1]

@Devin:

Thanks for the info, but can it be programmed to use the old fashioned CTRL V? It may sound silly that a new little adjustment like Shift+CTRL+V would be a problem, but believe me, with the amount of programs we use, data processed and ever-changing procedures, it would be frustrating since we could be using 6+ programs at one time, all using CTRL+V to paste.

So you imagine the built up frustration with one program that wants to do it's own thing!

@Stormseed
Are you saying that in fact, I can use Devon's code with CTRL+V, or just with the button?

I'd like to avoid any extra buttons besides the standard CTRL+V, as I explained above.

Thanks everybody for your help. I want to return to this post when I get some more important updates out of the way since we just got yet another procedure change!

 

[Devin]

In the macro code, the symbol ^ mean "ctrl" and + means "shift." So, just remove the + from your code and the ctrl v will work just like ctrl + shift v worked before.

 

[trevford1]

Grand!