Macro error

[Kim]

I've problem with the macro. Can someone help?

Sub Validation()

Sheets("Negotiation Tool Report").Select
Range("Q1:T1").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Criteria!$A$1:$A$7"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

End Sub

Thanks

 

[Jacob Skaria]

You cannot refer a different sheet directly; instead create a named range and
then refer..Try the below macro and feedback

Sub Validation()
Dim ws As Worksheet
Set ws = Sheets("Negotiation Tool Report")

ActiveWorkbook.Names.Add Name:="myRange", RefersTo:= _
Sheets("Criteria").Range("$A$1:$A$7")

ws.Range("Q1:T1").Validation.Delete
ws.Range("Q1:T1").Validation.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
Formula1:="=myrange"
End Sub

If this post helps click Yes