Excel2000: How to copy data validation using VBA

[Arvi Laanemets]

Hi

How doesn't the code from below work? I get Run-time error '1004':
PasteSpecial method of Range class failed!

....
Sheets("SheetName").Range("A11").Offset(RowNo - 2, 0).Copy
Sheets("SheetName").Range("A11").Offset(RowNo - 1, 0).PasteSpecial _
Paste:=xlDataValidation, Operation:=xlNone, SkipBlanks:=False,
Transpose:=False
....

It does work with xlPasteFormats
Sheets("SheetName").Range("A11").Offset(RowNo - 1, 0).PasteSpecial _
Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False,
Transpose:=False

I'm writing an Open event for workbook, which when needed adds new records
into table, and copies some data valdation lists from existing rows into new
ones.


Thanks in advance for any help

 

[Arvi Laanemets]

Solved (found a hint from web)!

Sheets("SheetName").Range("A11").Offset(RowNo - 1, 0).PasteSpecial _
Paste:=xlDataValidation, Operation:=xlNone, SkipBlanks:=False,
Transpose:=False

Sheets("SheetName").Range("A11").Offset(RowNo - 1, 0).PasteSpecial _
Paste:=6, Operation:=xlNone, SkipBlanks:=False, Transpose:=False