CODEWITHSUNDEEP
excelvba
Sarah
Sarah

Reputation: 13

How to exit Macro

Here is the code I have. I am reading from one sheet and searching for that term on another sheet. If the search finds the match, it will copy that whole row and paste it onto the first sheet. The search, copy, and paste work. I do not recieve an error but it is as if the macro does not stop because Excel won't let me click out of the original cell the macro read. Am I missing a line of code that should end the macro? Ideally, it should just end the macro after the paste.

I went off the code from another thread: How to Paste copied Row from one Sheet to Another

Sub Ref()

Dim rng1 As Range
Dim rng2 As Range

If Range("A11") = "SELECT" Then
    Else
    Set rng1 = Worksheets("MAIN").Range("A11")
    For Each rng2 In Worksheets("REF").Range("A11:A2000")

        If rng1 = rng2(1).Value Then
            rng2.EntireRow.Copy
            rng1.EntireRow.PasteSpecial (xlPasteFormulas)
            Exit For
        End If

    Next rng2
End If
End Sub

This is where I call the macro

Private Sub Worksheet_Selection_Change(ByVal Target As Range)
    Call Ref
End Sub

Thank you in advance

Upvotes: 1

Views: 456

Answers (1)

Tim Williams
Tim Williams

Reputation: 166755

You need to stop the paste from re-triggering the change event:

...
Application.EnableEvents=False
rng1.EntireRow.PasteSpecial (xlPasteFormulas)
Application.EnableEvents=True
...

EDIT: You should probably be using the worksheet_change event, not selection_change. You can handle the event enabling there instead:

Private Sub Worksheet_Change(ByVal Target As Range)
    If not application.intersect(me.range("A11"),Target) is nothing then
       Application.EnableEvents=False
       Call Ref
       Application.EnableEvents=True
    End if
End Sub

Upvotes: 1

Related Questions