Run a Macro when select an option in dropdown

[Gaurangg]

Hi Friends,

I need a little help in my code which is working in different logic. If I explain my scenario, I have an excel with two worksheets Sheet1(Form), Sheet2(Collation). I have a data in Sheet1 and kept a drop down list in column B (B2:B250) with Yes/No option. In column A there is a unique reference number of the data. What I need is.. whenever I select "Yes" in column B i.e. if I select "No" then it should be exit sub, but if I select "Yes"in B5, it should copy A5 and paste in A1 cell in "Collation" Sheet. irrespectively if I select "Yes" in B7, A7 should be copied to collation sheet A1.

I have used worksheet change event but every time I need to close and reopen the file after running the macro. Even if I delete the "Yes/No" in form sheet, the macro stop working while again selecting "Yes".

My current Code (in Sheet1)

Private Sub Worksheet_Change(ByVal Target As Range)


Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn, xOffsetColumn1 As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("B:B"), Target)
xOffsetColumn = -1




If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
        If Not VBA.IsEmpty(Rng.Value) Then
        Call Copycelldata
        Else
            Exit Sub
        End If
    Next
    Application.EnableEvents = True
End If
End Sub

In Module

Sub Copycelldata ()

        ActiveCell.Select
        ActiveCell.Offset(0, -1).Select
        Selection.Copy
        Sheet2.Select
        Range("A1").Select
        ActiveSheet.Paste

End Sub

I will be fine if only one code can be made. Also want to keep undo option working

 

[jmacleary]

Hi there. I think what is happening is that your code is terminating with the line 'Exit Sub' in the Else clause. Put 'Application.EnableEvents = True' befor it and see if that fixes it.

John

 

[Gaurangg]

Hi, It works perfectly. Thanks a lot mate. Also can you please help me to add a condition if I select "No", no action to be taken and when I select "Yes" from the dropdown then only the code should work? Can you please suggest where and what I should make amendments.

 

[jmacleary]

Replace 'Call Copycelldata' with:

IF Rng.Value="Yes" then
   Call Copycelldata
End If

 

[Gaurangg]

Oh, I was really tried with Rng.Value = "Yes" in the previous line. Thanks a lot mate. You are a starrrr

 

[jmacleary]

youre welcome. can you vote on the like buttons for me please.