PDA

View Full Version : Different ways of copying



chamster
09-17-2007, 10:51 PM
I'll be copying a number of cells and i wonder if/how one can specify the process in a more detail. For instance, can i perform a copy operation without bringing the format along? Can i specify the format of cells i copy to in the same command as i copy them? Keep going if you got some of your own approaches.

Generally, i'd like to specify all the possible parameters. Not because i think it's fun (i DO appreciate defaults, of course) but because i wish to learn which they are in case i'll be needing them one day.

Bob Phillips
09-18-2007, 12:06 AM
Look at Copy and PasteSpecial in help.

chamster
09-18-2007, 04:23 AM
PsasteSpecial seems to be a command on its own. I was going to use the .Range(X).Copy Destination:=.Range(Y) syntax that rory hinted me about. So, i was expecting more "somethings" as shown below.
.Range(X).Copy Destination:=.Range(Y) Something1:=a Something2:=b etc.

Should i skip the intention?

rory
09-18-2007, 04:26 AM
Yes, the only parameter you get with that is Destination.

Bob Phillips
09-18-2007, 04:29 AM
PsasteSpecial seems to be a command on its own. I was going to use the .Range(X).Copy Destination:=.Range(Y) syntax that rory hinted me about. So, i was expecting more "somethings" as shown below.
.Range(X).Copy Destination:=.Range(Y) Something1:=a Something2:=b etc.

Should i skip the intention?

Yes, that is why I suggested looking at PasteSpecial.

chamster
09-18-2007, 05:56 AM
Oh, NOW i get it. I went F2 on it and there it was - (some) info about the method. I got
Sub PasteSpecial([IconIndex], [Link], [Placement], [DisplayAsIcon], [DataType], [IconFileName], [IconLabel])
and what i wonder now - is there a way to list the different parameters INCLUDING a short description of what they do/are used for?

Also, to my surprise, there's no [Destnation] listed there. Does it mean that it's not available for PasteSpecial?! Or should i understand that it's there since PasteSpecial is a Paste, hence inheriting all the methods of the latter? As long as we're on the subject - should i view PasteSpecial as an extension/inheritance of Paste?

Bob Phillips
09-18-2007, 06:39 AM
Oh, NOW i get it. I went F2 on it and there it was - (some) info about the method. I got
Sub PasteSpecial([IconIndex], [Link], [Placement], [DisplayAsIcon], [DataType], [IconFileName], [IconLabel])
and what i wonder now - is there a way to list the different parameters INCLUDING a short description of what they do/are used for?

I get this from help myself

expression.PasteSpecial(Paste, Operation, SkipBlanks, Transpose)

expression Required. An expression that returns a Range object.

Paste Optional XlPasteType. The part of the range to be pasted.

XlPasteType can be one of these XlPasteType constants.
xlPasteAll default
xlPasteAllExceptBorders
xlPasteColumnWidths
xlPasteComments
xlPasteFormats
xlPasteFormulas
xlPasteFormulasAndNumberFormats
xlPasteValidation
xlPasteValues
xlPasteValuesAndNumberFormats

Operation Optional XlPasteSpecialOperation. The paste operation.

XlPasteSpecialOperation can be one of these XlPasteSpecialOperation constants.
xlPasteSpecialOperationAdd
xlPasteSpecialOperationDivide
xlPasteSpecialOperationMultiply
xlPasteSpecialOperationNone default
xlPasteSpecialOperationSubtract

SkipBlanks Optional Variant. True to have blank cells in the range on the Clipboard not be pasted into the destination range. The default value is False.

Transpose Optional Variant. True to transpose rows and columns when the range is pasted.The default value is False.



Also, to my surprise, there's no [Destnation] listed there. Does it mean that it's not available for PasteSpecial?! Or should i understand that it's there since PasteSpecial is a Paste, hence inheriting all the methods of the latter? As long as we're on the subject - should i view PasteSpecial as an extension/inheritance of Paste?

Why? Destination is an argument to Copy. Paste has no destination arguement, nor does Pastespecial as expression identifies the destination.

It inherits no methods, it is a method.

rory
09-18-2007, 07:07 AM
If you look up PasteSpecial in Help, you will see that it is a method of both the Worksheet and Range objects, with different arguments for each. The one you are interested in here is the Range.PasteSpecial method as per xld's post.

chamster
09-18-2007, 08:34 AM
My mistake. I thought of PasteSpecial as a variant of Copy. Probably a case of brain-deadness at the end of a day. Or just plain stupidity. I get it sometimes. Thanks, anyway.

As we're discussing the subject of being stupid - the PasteSpecial i got to see was through Word and it seems it was for an operation in Word not Excel. That solves this mystery. :)