I did - it is not there in Excel 2000 ... like I said in my original
post...neither xlPasteValidation nor xlDataValidation

"keepITcool" wrote:


xlXP records it (properly) as xlPasteValidation.

check Object browser for Enum Excel.XlPasteType



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Philip wrote :

I found an answer (you have to use 6 for xlDataValidation)

Selection.PasteSpecial Paste:=6

still, strange that even though the macro recorder can find the
enumerated value, it's not available in the object model and VBA
Compiler can't find it either!

Philip

"Philip" wrote:

Ok this is a little strange...

If you put a list validation (like 'Y/N' from another range) on a
cell, then using the macro recorder record the following actions:
1) copy
2) select destination range
3) PasteSpecial (choose Validation as the paste option)

stop recording.

You get code like this:
Selection.Copy
Range("F3:F8").Select
Selection.PasteSpecial Paste:=xlDataValidation,
Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False

Now, when you try to run this with 'Option Explicit' there is an
error 'variable not defined on the 'xlDatValidation' ! if you try
to run it without Option Explicit, the PasteSpecial method fails...

Not only that, if you search the Object Model (F2) for
'xlDataValidation' it doesn't exist!

Of course, xlPasteAll works, but it is strange that the macro
recorder can find 'xlDatValidation' but the VBA Compiler can't and
neither is it in the Object Model!

Lovely !

So I say this is a bug - and how do we get it fixed (or is it
alright in XP/2003) ?

thanks

Philip