Or for versions earlier than Excel 2002:
Paste:=6

XL97: Problems with Arguments of PasteSpecial Method
http://support.microsoft.com/default.aspx?id=171281

Debra Dalgleish wrote:
Try Paste:=xlPasteValidation

JarredW wrote:

Hi,
I'm wondering if anyone can help. I'm writing code in VBA in Excel to
copy only the data validation from a selected line in one workbook to
a range in another workbook. When i use my code it brings the error
message: "Paste Special Method of range class failed" My code is as
below:
Windows(file2).Activate Rows("3:3").Select Selection.Copy
Windows(File).Activate Range(Cells(1, 1), Cells(400,
13)).Select Selection.PasteSpecial Paste:=xlDataValidation,
Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False
I've looked in the Excel help, and this says that 'xlDataValidation'
is not an option for PasteSpecial. There must be a way to only paste
the validation! Any help would be appreciated.
Jarred.





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html