Purpose of Operation:=xlNone and modifying values before pasting

Hi
Hi

Can someone explain to me in simple language what is the purpose of following code lines:
Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False

Complete code line is as follows:
(Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False)

I am copying some values from an excel file and pasting them into the other.

Is it possible to modify the value before pasting them.
For example column B has absolute values and when this is copied and pasted into the column B in other sheet the new values should be in % (i.e. all values should be divided by 100)

hope my questions makes sense...
Cheers
J

Originally Posted by MatrixMan

Hi - your online help in VBA will have a better explanation, but:

Selection.PasteSpecial ....
Paste:=xlPasteValues : i.e. not pasting text or formulae; including or not formats and so on; this is the default.
Operation:=xlNone : i.e. multiply, divide, add etc whatever is in the range; default again.
SkipBlanks _ :=False : i.e. whether to paste blanks as well or skip them; default again.
Transpose:=False : i.e. copying rows to columns or the other way; default again.

Taking these out of you PasteSpecial command will have no effect as they are all the defaults.

Hope that helps. MM.

Many thanks for your reply. Can you please tell me the syntax for performing operation for example say that if i want to divide the value by 100 (to convert into %) what syntax do i write.

Sorry if this is too basic but i am a complete novice to VBA and do not have a decent book with me at the moment....

An internet search of 'excel vba pastespecial operation' will give you plenty of results. This is from the MSDN.

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

This example replaces the data in cells D1:D5 on Sheet1 with the sum of the existing contents and cells C1:C5 on Sheet1.

Please Login or Register  to view this content.

So at a guess you have 2 options. Either fill the entire range that you are going to paste to with '100' s, and then use the xlPasteSpecialOperationDivide, or what is probably easier is to just paste the values over as they are, and then run another section of code to divide all the values by 100.

Originally Posted by Phil_V

An internet search of 'excel vba pastespecial operation' will give you plenty of results. This is from the MSDN.



So at a guess you have 2 options. Either fill the entire range that you are going to paste to with '100' s, and then use the xlPasteSpecialOperationDivide, or what is probably easier is to just paste the values over as they are, and then run another section of code to divide all the values by 100.

Many thanks for your reply. Can you help me further by a little bit. Sorry if this is too basic but i am a complete novice..

As you said that first paste the values as they are and then run anotehr section of code to divide all values by 100. What would the code be for this second part i.e. say i have pasted the values in column A and then i need to update the values (such as dividing all by 100) how do i do that.

Many many thanks for your input and time.

If the values are going straight into columnA, and there's nothing else on that sheet you can do the following.
Let's say your data in in columnE (5) on sheet1, and you want to copy it to columnA (1) on sheet 2:

Please Login or Register  to view this content.