How to add an additional workbook in search range?

Question

I want to search for a string inserted in "TextBox1" within the range (A2,G2000) of data located in Workbook "officerA" Worksheet "DATA", and then paste results found into Workbook "Mainwb" sheet "MAIN SCREEN" Range (A5,G500).

I wrote this code quoting from many sources.

Code to search within same workbook:

Private Sub CommandButton1_Click()

    Dim wb1 As Workbook, Wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    
    Dim strSearch As String
    Dim f As Variant
    Dim fAddress As String
    Dim fRow As Long
    Dim cellA As Variant
    Dim cellB As Variant
     
        
    Set Wb2 = Workbooks.Open("C:\Users\elhayani\Desktop\development\AML db\OfficerA.xlsx") 
    Set wb1 = Workbooks.Open("C:\Users\elhayani\Desktop\development\AML db\Mainwb.xlsm")
    Set wb1 = ActiveWorkbook
    Set ws2 = wb1.Sheets("MAIN SCREEN").Range("A5:G2000")
    Set ws1 = Wb2.Worksheets("DATA")  
    
    strSearch = TextBox1.Value
    
    ws1.Range("A5:G2000").ClearContents
    Set dmr = Workbooks.Open("C:\Users\aselhayani\Desktop\Excel Reports\OfficerA.xlsx")
    Set dmr = Worksheets("DATA")
    strSearch = InputBox("Please enter T24 ID:", "Search Value")

    pasteRowIndex = 5

    If strSearch = vbNullString Then
        MsgBox ("User canceled, or did not enter a value.")
        Exit Sub
    End If

    With ws1.Range("A2:G2000")
    
        Set f = .Find(strSearch, LookIn:=xlValues)
        If Not f Is Nothing Then
            fAddress = f.Address
            Do
                fRow = f.Row
                cellA = ws2.Cells(fRow, 1).Value
                cellB = ws2.Cells(fRow, 2).Value
                cellC = ws2.Cells(fRow, 3).Value
                cellD = ws2.Cells(fRow, 4).Value
                cellE = ws2.Cells(fRow, 5).Value
                cellF = ws2.Cells(fRow, 6).Value
                cellG = ws2.Cells(fRow, 7).Value

                ws1.Cells(pasteRowIndex, 1) = cellA
                ws1.Cells(pasteRowIndex, 2) = cellB
                ws1.Cells(pasteRowIndex, 3) = cellC
                ws1.Cells(pasteRowIndex, 4) = cellD
                ws1.Cells(pasteRowIndex, 5) = cellE
                ws1.Cells(pasteRowIndex, 6) = cellF
                ws1.Cells(pasteRowIndex, 7) = cellG
                
                pasteRowIndex = pasteRowIndex + 1

                Set f = .FindNext(f)

            Loop While Not f Is Nothing And f.Address <> fAddress
        End If
    End With
    MsgBox "Search Done"
End Sub

Answer 1

You should pass the worksheet in the external workbook as a parameter into another sub routine for processing.

Option Explicit

Private Sub CommandButton1_Click()

    Const OfficerAPath = "C:\Users\best buy\Downloads\stackoverfow\temp\OfficerA.xlsx"
    Const OfficerBPath = "C:\Users\best buy\Downloads\stackoverfow\temp\OfficerB.xlsx"
    Dim wb As Workbook
    Dim strSearch As String

    strSearch = TextBox1.Value
    If strSearch = vbNullString Then
        MsgBox ("User canceled, or did not enter a value.")
        Exit Sub
    End If

    Worksheets("MAIN SCREEN").Range("A5:G2000").ClearContents

   ' Process Workbooks OfficerAPath
    Set wb = Workbooks.Open(OfficerAPath)

    SearchWorksheet wb.Worksheets("DATA"), strSearch

    wb.Close False

   ' Process Workbooks OfficerAPath

    Set wb = Workbooks.Open(OfficerBPath)

    SearchWorksheet wb.Worksheets("DATA"), strSearch

    wb.Close False

    MsgBox "Search Done"
End Sub

Sub SearchWorksheet(dmr As Worksheet, strSearch As String)
    Dim f As Range, SearchRange As Range
    Dim fAddress As String
    Dim pasteRowIndex As Long, y As Integer

    With dmr
        Set SearchRange = .Range("A1", .Range("A" & .Rows.Count).End(xlUp))
    End With

    With ThisWorkbook.Sheets("MAIN SCREEN")
        pasteRowIndex = .Range("A" & Rows.Count).End(xlUp).Row

        If pasteRowIndex < 5 Then pasteRowIndex = 5

        Set f = SearchRange.Find(strSearch, LookIn:=xlValues)

        If Not f Is Nothing Then
            fAddress = f.Address
            Do
                For y = 1 To 7
                    .Cells(pasteRowIndex, y) = dmr.Cells(f.Row, y).Value
                Next

                .Cells(pasteRowIndex, 8) = dmr.Parent.Name

                pasteRowIndex = pasteRowIndex + 1

                Set f = SearchRange.FindNext(f)

            Loop While Not f Is Nothing And f.Address <> fAddress
        End If
    End With
End Sub