LinkBack URL
About LinkBacks
Hello all, have an easy one today. I need to copy the value and format of a range of cells, is there a way to do this in one step rather this:
Thanks for your help everyone.
Please Login or Register to view this content.
Hello all, have an easy one today. I need to copy the value and format of a range of cells, is there a way to do this in one step rather this:
Thanks for your help everyone.Please Login or Register to view this content.
Last edited by MrHockey; 06-22-2009 at 03:25 PM.
I figured this one out...do not qualify what is being copied and you get it all...
I feel so stupid.
Glad you got it, but the oneline answer you were asking about would be:
No need to really "set" the block or "select" the destination.Please Login or Register to view this content.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Thanks J for that info, It provides yet another solution I was not aware of. When copying a cell, and since a cell can contain any of 3 attribute types (data; format; formula), it makes sense that VBA is able to provide 4 way sto manage these attributes (all; data only; format only; formula only). In code this would be:;Please Login or Register to view this content.;Please Login or Register to view this content.;Please Login or Register to view this content..Please Login or Register to view this content.
And then per your help, to combine values and format you would use.Please Login or Register to view this content.
Thanks J for you help, much appreciated. This forum is super.
Well, hold onto your hat, because you're only halfway there...
Truth is, if you want to get a full picture of the "paste special", in the regular worksheet click any cell, press CTRL-C, then click on EDIT > PASTE SPECIAL just to get this window open...look at ALL the fun things you can do with a pastespecial function...all that can be done in VBA, too.Please Login or Register to view this content.
I just tested "Range("D4").PasteSpecial xlPasteValuesAndNumberFormats" and it is not working. I have submitted a workbook with this posting, would you please review it and show me what I am doing wrong.
Thanks again for the help.
This post is in reply to "hold on to your hat"...
I see what you mean J, I was just focused on the type of data that can exist in a cell, not the types of pastespecial. Thanks for the pointer. As you can see by my previous post I am still having trouble.
You mucked up the copy command ranges, swapped the Sample1 and the target...that's why it's not working.
Once you fix that, it WILL copy, but it still won't do what you want.
To copy into cells with validation, I would keep ONE cell with validation in it handy for a followup copy / validation command.
Please Login or Register to view this content.
Last edited by JBeaucaire; 06-10-2009 at 10:44 PM.
Thanks for the help J. And sorry for the slow reply, have been super busy.
Hi guys. Jbeau's oneline solution works fine for me; but how about doing the same thing from one sheet to another guys?
Hello Lee Calas, and welcome to the forum.
Unfortunately you have inadvertently broken one of the forum rules. Please read the following and make the necessary change. Thanks.
Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks