Copying only data validation

[Guest]

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.

 

[Melanie Breden]

Hi Jarred,

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"

the validation cannot be copied.
If different validation are, you have to create new validations
for each cell validation from one range into the other range:

Sub MyValidation()
Dim rngSource As Range
Dim rngTarget As Range
Dim intCol As Integer
Dim objVal As Validation
Dim bln As Boolean

Set rngSource = Workbooks("File2.xls").Worksheets(1).Range("A3:M3")
Set rngTarget = Workbooks("File.xls").Worksheets(1).Range("A1:M400")

Set rngSource = Workbooks("Validation Ãœbertragen.xls").Worksheets(1).Range("A3:M3")
Set rngTarget = Workbooks("Hyperlink Pfad ändern.xls").Worksheets(1).Range("A1:M400")

For intCol = 1 To rngTarget.Columns.Count
Set objVal = rngSource.Cells(intCol).Validation
Err.Clear
On Error Resume Next
bln = objVal.AlertStyle = 1
If Err = 0 Then
With rngTarget.Columns(intCol).Validation
.Add Type:=objVal.Type, _
AlertStyle:=objVal.AlertStyle, _
Operator:=objVal.Operator, _
Formula1:=objVal.Formula1, _
Formula2:=objVal.Formula2
.InputTitle = objVal.InputTitle
.ErrorTitle = objVal.ErrorTitle
.InputMessage = objVal.InputMessage
.ErrorMessage = objVal.ErrorMessage
End With
End If
Next intCol
End Sub

--
Regards
Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)

 

[Debra Dalgleish]

Try Paste:=xlPasteValidation

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]

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

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