PDA

View Full Version : [SOLVED] Copy, destination & paste special



faye
11-23-2005, 11:23 PM
Hi all, is it true that copy, destination and paste special don't work together???
i'm trying to put them all together in my code but i get errors all the time..... i wanted the code to just paste only the value of copied range(B10:S10) into a single starting cell (A1) instead of using A1:A10.

My codes:


Set rng = wb.Worksheets(Sheet1).Range("B10:S10")
rng.Copy Destination:=Range(A1).PasteSpecial(xlPasteValues

Thanks you.

BlueCactus
11-23-2005, 11:36 PM
Without trying it for myself, your code looks wrong because it specifies the destination as Range.PasteSpecial instead of Range. AFAIK, to use PasteSpecial you must first do a separate Copy.

MikeWolfeJr
11-23-2005, 11:37 PM
Two things...
1. Use PasteSpecial as a method of the destination cell.
2. Use Cells() not Range()


Set rng = wb.Worksheets(Sheet1).Range("B10:S10")
Set destWks = wb.Worksheets(Sheet2)
rng.Copy
destWks.Cells(1,1).PasteSpecial Paste:=xlPasteValues
Hope that helps!!

faye
11-24-2005, 12:40 AM
Thanks for the super quick response. Mike Wolfe, your proposed solution is great. However, there is a reason that i used "range()" instead of "cell()".

I did a Macro in the first place to determine the position to paste these data and sadly the returned value of that Macro is the column address which is all these column alphabets.

And that value will be passed into range().

look at the codes below to better understand what i said.


Set rng = wb.Worksheets(Sheet1).Range("B10:S10")
rng.Copy Destination:=Range(col & "1").PasteSpecial(xlPasteValues)

the "col" is the return value

thanks

BlueCactus
11-24-2005, 12:48 AM
There is nothing wrong with using a single cell address in the form of Range("A1") as the destination. The key with PasteSpecial is that it requires separate .Copy and .PasteSpecial methods.

BTW. I do often use .Cells() as I find it easier to programmatically define an address with .Cells(). But there are many ways to skin a cat, as they say.

faye
11-24-2005, 01:35 AM
:yes okay, i got your point, BlueCactus. So is there any suggestion i should do with my codes. As i am quite new in VBA, i only know of the copy & destination method....

faye
11-24-2005, 06:48 PM
I've figured out the solution all thanks to the you guys.


Set rng = wb.Worksheets(sh).Range("B9:S9")
rng.Copy
Range(col & h).PasteSpecial (xlPasteValues)

Thanks again :)

MikeWolfeJr
11-24-2005, 10:02 PM
Ahh yes, BC. Thanks for the correction. I guess I'm just so use to using .Cells(), else run into programmatic issues like you were saying.

For some reason, I seem to inevitably wind up with rediculously long, confusing, nested range/cell loopification codage somewhere.

BlueCactus
11-24-2005, 10:58 PM
Hehe. We're probably in the minority on this board for using the .Cells() approach. Don't see it much in other people's code. I've always been a big fan of .Cells().Resize() because I'm always moving data in and out of variants containing arrays. You can always turn it into a normal-looking range with .Cells().Resize().Address