Paste Values only

trevford1

New Member
Joined
Feb 22, 2009
Messages
14
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.
 

trevford1

New Member
Joined
Feb 22, 2009
Messages
14
Thanks for the response. I've been busy and had to put off this part of the code for now.
 

Devin

Board Regular
Joined
Jan 21, 2009
Messages
105
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

Banned
Joined
Sep 18, 2006
Messages
3,274
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 :biggrin:

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

New Member
Joined
Feb 22, 2009
Messages
14
@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

Board Regular
Joined
Jan 21, 2009
Messages
105
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.
 

Forum statistics

Threads
1,081,418
Messages
5,358,559
Members
400,504
Latest member
RedSquirrel

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top