That is exactly what I was looking for, with a slight modification I was able to apply the validation to a range of 12 worksheets. A thousand thanks!
'Best method!!!
Dim N As Long Dim WS As Worksheet
For N = 1 To ThisWorkbook.Worksheets.Count - 3 'added the -3 to exclude other worksheets Set WS = ThisWorkbook.Worksheets(N)
With Range("A10:A14,A16:A21,A23:A33,A35:A41,A50:A54,A56:A61,A63:A73").Validation 'Apply validation to this ranges of cells .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=Key!$C$2:$C$100" 'Retrieves the list from another worksheet named keys .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True
End With
I did figure out an alternate with .select, however it would flip through all the worksheets when ever I opened excel. I am posting this because the.select method is all over the internet and I DO NOT!! recommend using the method below.
Dim N As Long For N = 1 To ThisWorkbook.Worksheets.Count - 3 ThisWorkbook.Worksheets(N).Select
If you are a Visitor, go ahead and post your reply as a
new comment, and we'll move it here for you
once it's approved. Be sure to use the same name and email address.
This thread is now CLOSED. If you wish to comment, start a NEW discussion in
Excel Forum.