Excel2000: How to copy data validation using VBA

A

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("A1:D1").Offset(RowNo - 2, 0).Copy
Sheets("SheetName").Range("A1:D1").Offset(RowNo - 1, 0).PasteSpecial _
Paste:=xlDataValidation, Operation:=xlNone, SkipBlanks:=False,
Transpose:=False
....

It does work with xlPasteFormats
Sheets("SheetName").Range("A1:D1").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
 
A

Arvi Laanemets

Solved (found a hint from web)!

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


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