Pasting 'Value Only' in Excel

Hi,

I have a directive to write VBA to "past value only" if a user copies one cell and pastes it into another. The client doesn't want the first cell's formatting pasted into the second cell so the second cell becomes a currency instead of a percentage, and that sort of thing. Also, pasting values only will resolve the foreseeable problem of a user copying/pasting a formula, most likely wanting the formula's result, not the forumula itself, pasted into the second cell.

I know how to paste a Value Only manually, but I have no clue how to begin doing it with VBA.
Hope someone can help!

Thanks!
monboisAsked:
Who is Participating?
 
gowflowConnect With a Mentor Commented:
change this
Sheets("SheetName").Range(ActiveCell).PasteSpecial Paste:=xlValues
by this
Sheets("SheetName").Range(ActiveCell.adress).PasteSpecial Paste:=xlValues

gowflow
0
 
Saqib Husain, SyedEngineerCommented:
The simplest is to assign the value

Range("A2").value = Range("A1").value

will put the value of A1 in A2 without affecting anything else
0
 
andrewssd3Commented:
The simplest way of doing this, which avoids using the clipboard altogether, is just to use the Value property:
    Range("B1").Value = Range("A1").Value

Open in new window

0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
andrewssd3Commented:
Like he just said...
0
 
gowflowCommented:
Well you can also do this
Range("A1").copy
Range("B1").pastespecial Paste:=xlvalues

will only copy what in A1 to B1 without the formulas without the format
gowlfow
0
 
monboisAuthor Commented:
Thanks, guys, but I'm talking about VBA that will take the value of any cell a user copies from any tab and then pastes into any cell in any other tab - or the same tab! It can't be as specific as A1 to B1.
0
 
Saqib Husain, SyedEngineerCommented:
Sheets("Sheet1").Range("A2").value = Sheets("Sheet2").Range("A1").value
0
 
gowflowCommented:
well here it is
sheets("Sheet1").Range("A1").copy
Sheets("Sheet2").Range("B1").pastespecial paste:=xlvalues

gowflow
0
 
monboisAuthor Commented:
Thanks, ssaqibh, but again, I cannot specify the cell being copied (cell 1) nor the cell being pasted into (cell 2). The VBA will behind the tab of cell 2.

I'm trying to write comething with ActiveCell.Value but I still haven't figured it out.
0
 
gowflowCommented:
hv u tried my solution ?
gowflow
0
 
gowflowCommented:
I read your last thread
---------
I cannot specify the cell being copied (cell 1) nor the cell being pasted into (cell 2). The VBA will behind the tab of cell 2.
I'm trying to write comething with ActiveCell.Value but I still haven't figured it out.
---------
Its not clear what you want to acheive can you be more specific with a clear example like I hv value in cell A1 on sheet ... I want to copy or when user clik on .... then paste on etc ...
gowlfow
0
 
dlmilleCommented:
@monbois - are you asking for the code that allows only paste values, independent of what the user does?  Do you just want their default paste to be paste values, or ANY paste to be paste values?  I assume this is for a given workbook (and not for any workbook they work on).  Is all this correct?

Here's code that will disable controls - select those you want to disable, and have the code run on workbook_open event of your workbook where you want to manage this:

http://www.xcelfiles.com/VBA_Quick13.html

If this is more in line with what you're looking for, let me know what version of Excel you're working with and I can help you customize from here.

Dave
0
 
monboisAuthor Commented:
Hi gowflow,

Altering your solution to:

     Sheets("SheetName").Range(ActiveCell).PasteSpecial Paste:=xlValues

Doesn't work, unfortunately. I get the msg:

     Application-defined or object-defined error.

I think I also need some kind of IF statement to identify the action of pasting, but the VBA won't accept ActiveCell.Paste, just ActiveCell.PasteSpecial, which is not likely to be the case.

ssaqibh and/or andrewssd3,

Though I'm pretty sure ActiveCell identifies A1 in your formula:

     Range("B1").Value = Range("A1").Value

How would I identify B1 in such a on-specific way?

Thanks!

0
 
dlmilleCommented:
@monbois - see my post - it looks at the action of pasting, amongst other things.  Is this what you're looking for?

Dave
0
 
dlmilleCommented:
@monbois - here is a much more thorough approach to catch paste operations:

http://www.jkp-ads.com/Articles/CatchPaste.asp

let me know when you've had time to digest and whether you'd like assistance in this area.

Dave
0
 
gowflowCommented:
dlmille
Nice Article I think this is what mombois want to reach but falied to explain. Anyway will wait for his comments.
gowflow
0
 
monboisAuthor Commented:
goflow,

I don't think there's any need to be insulting. I could just as easily accuse you of failing to comprehend my thoroough explanation. I'm not paying to be needlessly insulted.
0
 
Davy2270Commented:
I found this genius code by Aaron Bush. It gives you the code to enable copy paste values only. Run this code and Excel will only paste values.
Also the code for disabling paste values only is in there. Always nice to have ;-)
 
http://www.vbaexpress.com/kb/getarticle.php?kb_id=957

Tip: Put this code in an auto_open/auto_close macro.

Regards,
Davy
0
 
gowflowCommented:
monbois you mis-interpreted my comment, I absolutely did not mean any harm nor insult I don't know why you took it this way. I am deeply sorry if you felt this way that was not my intention. I simply meant to see what did you want exactly by cut and paste !
gowflow
0
 
gowflowCommented:
Actually I meant
====
dlmille
Nice Article I think this is what mombois want to reach but I falied to understand. Anyway will wait for his comments.
gowflow
====
Again sorry if you felt offended not at all my intention.
gowflow
0
 
monboisAuthor Commented:
dlmille & Davy2270,

Thank you for the links. Both processes work great to past values. Unfortunately the "undo" code in the one doesn't work with my file because it has to churn through so much info and takes forever, but I think I'll be able to use the warning msg from the other code to let users know they can't under what they're about to do.

That's all fine, but I'm stil concerned about users who utse the mouse and buttons to copy/paste without realizing they have to paste "values" only. Neither set of code works for this.

Any suggestions on how to tweak the code?

gloflow - Thank you for clearing up the misunderstanding. I'll be the first to admit I'm a dummy with a lot of this stuff or else I wouldn't be seeking help. I really do appreciate everyone's advice.

Thanks.
0
 
Davy2270Commented:
If you copy the code from the website I referred to above, and then run the macro 'ForcePasteSpecial', your excel application will always paste values, whether you use mouse, keyboard shortcuts or toolbar buttons.

As an addition copy this code also in your module:

Sub Auto_Open()
Call ForcePasteSpecial
End Sub

Sub Auto_Close()
Call ReleasePasteControl
End Sub

The excel workbook will automatically activate the 'paste values only' behaviour on opening the workbook. And it will disable it on closing.

Regards,
Davy
0
 
monboisAuthor Commented:
Thanks, Davy,  but unfortunatley that doesn't prevent a user from clicking "copy" and then clicking "paste" and pasting the first cell's formatting along with it's value. The VBA in the file doesn't address that. The code needs to be enhanced somehow.
0
 
dlmilleCommented:
To be efficient, please advise what version of Excel are you needing this for?

Dave
0
 
dlmilleCommented:
I've reviewed the Excel 2007/2010 version of catchPaste and its quite thorough, leaving the user/developer to further customize.  When the user does a regular paste, there's a warning that paste values will be done instead, so the user has the ability to cancel the paste.  This occurs with any of the commandbar/mouse menus, keystrokes, etc.  I don't believe you're able to do a paste without that pop-up warning, with the catchPaste version - with the exception of the PasteSpecial command which can also be disabled - and I've done that.  Apparently the original author made an error in the CustomUI portion.

Please try this and let me know what is not working for you and if you'd like alternate behavior in certain circumstances.  Once you think this will work for you, I can help you customize behavior, as desired.

The attached for Excel 2007/2010 use.

Dave


CatchPasteDemo.xlsm
0
 
gowflowCommented:
did you find your solution or still need help ?
gowflow
0
 
monboisAuthor Commented:
n/a
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.