Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Pasting 'Value Only' in Excel

Posted on 2011-10-17
27
Medium Priority
?
729 Views
Last Modified: 2012-05-12
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!
0
Comment
Question by:monbois
  • 9
  • 7
  • 5
  • +3
27 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 36981579
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
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36981602
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
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36981606
Like he just said...
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 31

Expert Comment

by:gowflow
ID: 36981827
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
 

Author Comment

by:monbois
ID: 36982025
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 36982054
Sheets("Sheet1").Range("A2").value = Sheets("Sheet2").Range("A1").value
0
 
LVL 31

Expert Comment

by:gowflow
ID: 36982057
well here it is
sheets("Sheet1").Range("A1").copy
Sheets("Sheet2").Range("B1").pastespecial paste:=xlvalues

gowflow
0
 

Author Comment

by:monbois
ID: 36982079
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
 
LVL 31

Expert Comment

by:gowflow
ID: 36982094
hv u tried my solution ?
gowflow
0
 
LVL 31

Expert Comment

by:gowflow
ID: 36982131
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
 
LVL 42

Expert Comment

by:dlmille
ID: 36982208
@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
 

Author Comment

by:monbois
ID: 36982212
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
 
LVL 42

Expert Comment

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

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36982259
@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
 
LVL 31

Accepted Solution

by:
gowflow earned 500 total points
ID: 36982385
change this
Sheets("SheetName").Range(ActiveCell).PasteSpecial Paste:=xlValues
by this
Sheets("SheetName").Range(ActiveCell.adress).PasteSpecial Paste:=xlValues

gowflow
0
 
LVL 31

Expert Comment

by:gowflow
ID: 36982417
dlmille
Nice Article I think this is what mombois want to reach but falied to explain. Anyway will wait for his comments.
gowflow
0
 

Author Comment

by:monbois
ID: 36982700
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
 
LVL 3

Expert Comment

by:Davy2270
ID: 36982725
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
 
LVL 31

Expert Comment

by:gowflow
ID: 36982803
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
 
LVL 31

Expert Comment

by:gowflow
ID: 36982826
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
 

Author Comment

by:monbois
ID: 36983045
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
 
LVL 3

Expert Comment

by:Davy2270
ID: 36983118
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
 

Author Comment

by:monbois
ID: 36983539
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
 
LVL 42

Expert Comment

by:dlmille
ID: 36983708
To be efficient, please advise what version of Excel are you needing this for?

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36983813
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
 
LVL 31

Expert Comment

by:gowflow
ID: 37364020
did you find your solution or still need help ?
gowflow
0
 

Author Closing Comment

by:monbois
ID: 37633158
n/a
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question