Copy, destination & paste special

[faye]

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]

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]

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]

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]

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]

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]

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]

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]

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