VBA: How to refer to the right worksheet
- by stanigator
Sub Macro1()
'
' Macro1 Macro
'
'
Worksheets("Drop-down").Select
n = Cells(1, 1).End(xlDown).Row
For i = 1 To n
ActiveSheet.Cells(i, 2).Select
If Worksheets("Misc").Cells(2, i).Value <> "" Then
If Worksheets("Misc").Cells(3, i).Value <> "" Then
Set validationRange = Range(Worksheets("Misc").Cells(2, i), Worksheets("Misc").Cells(2, i).End(xlDown))
Else
Set validationRange = Worksheets("Misc").Cells(2, i)
End If
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=validationRange.Address
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End If
Next i
End Sub
The lines after ActiveSheet.Cells(i,2).select within the for loop is not referring to the correct worksheet I want when I rechecked the settings for the validation drop-down menu. What is the easiest way of correcting this setback? Thanks in advance.
