I have a macro for comparing ranges to find matching data. These ranges can be in the same worksheet, different worksheets, or even different workbooks.

This macro is available in the Excel ribbon via users’ Personal.xlsb file.

Sometimes, when the macro finishes, the user finds themselves in the ‘other’ workbook as opposed to the workbook they were in when running the macro. This is not the expected behaviour, so users can get a little disoriented by this.

What change do I need to make in my visual basic code to ensure the macro returns the user to whatever workbook they were in when they first ran the macro?

Here is the code:

Sub FindMatchingDataV2()
'This macro was designed to allow users an easy way to find
'matching data between two ranges, either within the same worksheet or across
'worksheets within the same workbook, or even between two separate workbooks.

On Error GoTo Error_handler:

    Application.EnableEvents = False
    Application.Calculation = xlManual
    Application.ScreenUpdating = True
    
    
    
    Dim MySearchRange As Range
    Dim c As Range
    Dim findC As Variant
    
    Set myrange = Application.InputBox( _
    Prompt:="Select the range of cells containing the data you are looking for:", Type:=8)
    
    Dim myRangeArray As Variant
    myRangeArray = myrange.Value
    
    Set MySearchRange = Application.InputBox( _
        Prompt:="Select the range you wish to investigate:", Type:=8)
    
    Dim MSRArray As Variant
    MSRArray = MySearchRange.Value
    
    Dim Response As String
    Response = InputBox(Prompt:="Specify the comment you wish to appear to indicate the data was found:")
    
    myoutputcolumn = Application.InputBox( _
    Prompt:="Enter the alphabetical column letter(s) to specify the column you want the message to appear in.")
        
    Dim outArray As Variant
    ReDim outArray(1 To UBound(myRangeArray, 1), 1 To 1)
        
    Set sht = myrange.Parent
    
    Dim i As Long
    For i = 1 To UBound(myRangeArray, 1)
        Dim j As Long
        For j = 1 To UBound(MSRArray, 1)
            If myRangeArray(i, 1) = MSRArray(j, 1) Then
                outArray(i, 1) = Response
                Exit For
            End If
        Next j
    Next i
    
    sht.Cells(myrange.Row, myoutputcolumn).Resize(UBound(outArray, 1), 1).Value = outArray
            
    sht.Activate
    sht.Range("A1").Select
    

    Application.EnableEvents = True
    Application.Calculation = xlAutomatic
    Application.ScreenUpdating = True
    
    MsgBox "Investigation completed."


Exit Sub
Error_handler:
MsgBox "This macro will now close."

End Sub