Paste:=xlDataValidation error

[SunGodly]

I am a little bit lost on this one... I have a macro to paste the data validation rules from one cell to another but Paste:=xlDataValidation returns a "Variable not defined" error. Any ideas?

Thanks!

 

[xlbo]

DataValidation only applies to PASTESPECIAL and it is not a constant in its own right. YUou need to either use range("A1:a10&quot.pastespecial Paste:=xlAllExceptBorders
OR
jsut use a normal copy/paste - this will take validation over with it automatically
HTH Rgds
~Geoff~

 

[SunGodly]

My fault, I've caused some confusion by abbreviating my code. The actual line is

Selection.PasteSpecial Paste:=xlDataValidation

What is strange is I got this code directly from a recorded macro...Any other ideas?

 

[xlbo]

Weird - when I tried it out, selecting data validation from the paste special menu, I got xlAllExceptBorders - what version of excel are you using ?

Question is - why use paste special....either just use normal copy & paste (unless you already have values in the copy from cell or use the validation object
with selection.validation
mType = .type
mInputMsg = .inputmessage
mErrMsg = .errormessage
end with

with Range("RangeToPasteTo&quot.validation
.add type = mType
.inputmessage = mInputMsg
.errormessage = mErrMsg
end with

HTH
Rgds
~Geoff~

 

[SunGodly]

I'm using Excel 2000. I can't use the normal copy and paste because I do have values in the target cell. Also, I can't use the validation object because the validation rules I want to copy are variable (it's kind of an involved process - I'll spare you the details!

I haven't checked the MS site yet but I have checked a few books and I can't even locate "xlDataValidation" in any of them. I'm afraid this is just an Excel irregularity that doesn't have an answer but I appreciate your help. If you think of anything else, please let me know.

Thanks,
Scott

 

[xlbo]

Try replacing xlDataValidation with xlAllExceptBorders
This pastes the validation but not the value in the cell (tested on excel 97)
HTH Rgds
~Geoff~

 

[SunGodly]

Just tested your last suggestion and, at least in excel 2000, the value is pasted as well. I guess I could assign variables to each of the validation rules in the original cell and use the validation object (your first suggestion) to set the validation rules in the target cell. Seems like it would work but I would have expected an easier way! I'll give it a try and let you know how it turns out.

Thanks again.

 

[xlbo]

Cheers - maybe someone who's got e2000 can help out on this one - is xlDataValidation a known constant ??? Rgds
~Geoff~