Copying only data validation
 

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.

Copying only data validation
 

Hi Jarred,

JarredW wrote:
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)

Copying only data validation
 

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

Copying only data validation
 

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