Search & copy function (macro)

Option Explicit

Sub searchAndCopy()
    
    Dim activeRow           As Long
    Dim activeName          As String
    Dim matchRow            As Long
    Dim sourceSheet         As String
    Dim inputSheet          As String
    
    sourceSheet = "Sheet1"
    inputSheet = "Sheet2"
    
    activeRow = ActiveCell.Row
    activeName = Sheets(inputSheet).Cells(activeRow, "A")
    
    With Sheets(sourceSheet)
        matchRow = getItemLocation(activeName, .Columns(1))
        If (matchRow = 0) Then
            MsgBox "Error, unable to find name " + activeName, vbExclamation, "Error"
            Exit Sub
        End If
        .Cells(matchRow, "D") = Sheets(inputSheet).Cells(activeRow, "B")
        .Cells(matchRow, "E") = Sheets(inputSheet).Cells(activeRow, "C")
    End With
End Sub


Public Function getItemLocation(sLookFor As String, _
                                rngSearch As Range, _
                                Optional bFullString As Boolean = True, _
                                Optional bLastOccurance As Boolean = True, _
                                Optional bFindRow As Boolean = True) As Long
                                   
   'find the first/last row/column  within a range for a specific string
      
   Dim Cell             As Range
   Dim iLookAt          As Integer
   Dim iSearchDir       As Integer
   Dim iSearchOdr       As Integer
         
   If (bFullString) _
   Then
      iLookAt = xlWhole
   Else
      iLookAt = xlPart
   End If
   If (bLastOccurance) _
   Then
      iSearchDir = xlPrevious
   Else
      iSearchDir = xlNext
   End If
   If Not (bFindRow) _
   Then
      iSearchOdr = xlByColumns
   Else
      iSearchOdr = xlByRows
   End If
         
   With rngSearch
      If (bLastOccurance) _
      Then
         Set Cell = .Find(sLookFor, .Cells(1, 1), xlValues, iLookAt, iSearchOdr, iSearchDir)
      Else
         Set Cell = .Find(sLookFor, .Cells(.Rows.Count, .Columns.Count), xlValues, iLookAt, iSearchOdr, iSearchDir)
      End If
   End With
         
   If Cell Is Nothing Then
      getItemLocation = 0
   ElseIf Not (bFindRow) _
   Then
      getItemLocation = Cell.Column
   Else
      getItemLocation = Cell.Row
   End If
   Set Cell = Nothing

End Function