xlPasteValues 2

[Zygor]

Does anyone knoe the numeric value of xlPasteValues?

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

It seems using late binding and using CreateObject("Excel.Application") the code doesn't like xlPasteValues.

 

[Markus4]

-4163.
In the Direct Window you just put in "?xlpastevalues", hit [Enter], and there you are.

 

[PHV]

Replace this:
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
With this:
yourXLAPPobject.Selection.PasteSpecial Paste:=-4163, Operation:=-4142, SkipBlanks:=False, Transpose:=False

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Zygor]

What can I say? A star for a star!

I had tried 1 though 50 and had gotten nowhere...-4163 would have taken me a lifetime! Thanks!

 

[firefytr]

I always wonder why xlPasteValues is used via VBA. I don't know why MS even put it in there. The only reason I can think of is that so it could be used with the macro recorder.

If you are only pasting values, you do not need to use any sort of copy/paste at all. Compare these two pieces of code...

'...
'set 1
cells(1, 1).copy
cells(2, 1).pastespecial xlpastevalues

'...
'set 2
cells(2, 1).value = cells(1, 1).value

Do you see what is happening? Set 1 copies and pastes values. A value transfer only, which is what xlpastevalues does, right? What is set 2 doing? It transfers values. The same exact thing, except that you are not using the copy method, which takes up resources. The less you use the copy method the better off you'll be.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[Zygor]

A twist on this. What is the value for xlPasteFormats?

 

[firefytr]

Pasting formats is just that - pasting the format(s) of the copied cell(s). If you only paste formats, you do not transfer the value as Excel distinguishes between those items associated with cell(s) ranges.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[ClulessChris]

Markus4
I liked your post. Not only did you provide the answer, but also a method to find answers to similar issues.
I'll give you a star for that.

Don't count the days, Make the days count.

 

[Zygor]

Thanks for trying but, in the beginning of this post I wrote-

Does anyone knoe the numeric value of xlPasteValues?

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

It seems using late binding and using CreateObject("Excel.Application") the code doesn't like xlPasteValues.


Now I need to do the same with xlPasteFormats. PHV gave me the numeric value I needed for xlPasteValues and now I need the numeric value for xlPasteFormats.

 

[ClulessChris]

Zygor,
Read the post from Markus4
-4122

Don't count the days, Make the days count.

 

[combo]

You could use early binding or open excel and display VBE window. Now in object browser you can find any constant and its value.
Without reference to excel library 'xlPasteValues' in the best case is uninitialised variant value (vbEmpty).

combo

 

[Markus4]

ClulessChris,

thank you very much.

M.