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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run-time error 1004 paste method of worksheet class failed 3
Help with condensing a lot of copying/pasting... 4
Would like a more efficient way to Copy/PasteSpecial 4
Data Validation won't copy 7
conso macro 0
copy/paste in the next column 4
define Range in vba using contents of a cell that is named 1
a few adjustment for a do-loop escape 4

Top