starsky
07-03-2009, 08:52 AM
Hello,
I have a data that will be split into seperate worksheets in the same book. To these new sheets I will be applying macros. This is fine so far, I have macros that work across all the sheets.
What I want to do is insert some code that will apply data validation to all the sheets to a particular column ("O" from 010). As the number of records will vary from sheet to sheet, I'm presuming a dynamic range will need to be used - I want the validation limited to data rows.
Here is what I have so far. It gives me a run time error 91 at 'With .Validation'.
Dim rList As String
Dim Rge As Range
rList = "$Z$11:$Z$19"
'define dynamic range in col O
ActiveWorkbook.Names.Add Name:="Rge", RefersToR1C1:= _
"=OFFSET(WS.Name!R1C15,0,0,COUNTA(WS.Name!C15),1)"
With Rge
With .Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & rList
End With
End With
Any pointers greatly appreciated. Thanks.
I have a data that will be split into seperate worksheets in the same book. To these new sheets I will be applying macros. This is fine so far, I have macros that work across all the sheets.
What I want to do is insert some code that will apply data validation to all the sheets to a particular column ("O" from 010). As the number of records will vary from sheet to sheet, I'm presuming a dynamic range will need to be used - I want the validation limited to data rows.
Here is what I have so far. It gives me a run time error 91 at 'With .Validation'.
Dim rList As String
Dim Rge As Range
rList = "$Z$11:$Z$19"
'define dynamic range in col O
ActiveWorkbook.Names.Add Name:="Rge", RefersToR1C1:= _
"=OFFSET(WS.Name!R1C15,0,0,COUNTA(WS.Name!C15),1)"
With Rge
With .Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & rList
End With
End With
Any pointers greatly appreciated. Thanks.