copying validation

[tonymccann]

If I record a macro to copy validation details only from one row or cell to another using Paste/Special/Validation the macro records fine and the code looks like
eg
Rows("41:41").Select
Selection.Copy
Rows("43:43").Select
Selection.PasteSpecial Paste:=xlDataValidation, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

if I try and run the macro I get a response

"Run time error 1004
Paste Special method of rangeclass failed".....why??

any quick clever ways of copying validations??

[lucas]

Hi Tony,
I have moved your question to the excel forum as it appears to be a question about Excel.

[lucas]

I'm not sure I understand what your trying to do. If your trying to copy cells that have data validation from a list, this works for me.

[VBA]
Rows("41:41").Select
Selection.Copy
Range("A43").Select
ActiveSheet.Paste
[/VBA]

[lucas]

Unless you are trying to reset the validation to 0 or empty in the newly copied validation cells.....?

[vba]
Rows("41:41").Select
Selection.Copy
Range("A43").Select
ActiveSheet.Paste
Rows("43:43").Select
Selection.ClearContents
Range("A39").Select
[/vba]

[tonymccann]

My query is a VBA query not an Excel query - I want to copy validation and only validation information between cells within a macro. In excel I do it by copy then paste special then clicking on the validation only option. If you record this sequence as a macro it seems to record Ok but will not run as a macro giving the error i mention