PasteSpecial "Paste:=xlDataValidation" Not Valid? 1

[origapizni]

I recorded this but when I play it back, it fails with
Runtime error '1004': 'PasteSpecial method of Range class failed'. I can't locate the xlDataValidation constant in my Object Browser, but all the other "Paste:=" constants show up. Is this a known bug in Excel? Any ideas? Anyone else experienced this?

Sub Macro4()
Rows("28:28&quot.Select
Selection.Copy
Rows("27:27&quot.Select
Selection.PasteSpecial Paste:=xlDataValidation, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

Using Office 2k, Excel 2k, Win 2k

Thanks in advance!

 

[Zathras]

I get the same result. Very strange. It can be recorded but is invalid in playback. Using Excel 2K on Win 98.

At least xlPasteAll works and does past the validation(s). I guess you have a choice: Paste all and undo what needs to be undone or set up the validations after pasting.

Maybe someone else here can find a way.

 

[xlbo]

I had this crop up a little while ago - it was an error in xl97 but seems like it's not been sorted yet.
When you use PasteSpecial Validation in 97, what is recorded is xlAllExceptBorders

What you can do is just use the constant that the xl..... refers to which, in this case, I believe is 6
So your code would be

.pastespecial Paste:=6

rather than
.pastespecial Paste:=xlDataValidation

HTH Rgds
Geoff

Si hoc legere scis, nimis eruditionis habes

 

[combo]

This bug was fixed in excel XP. The value of this constant is still 6, name: xlPasteValidation.