Search string from multiple sheets

Hi All,
I have a small requirement where I need to search quite a few excel sheets for a particular string. Once I find it. I need the compete row to be copied to the search file.
I have attached the files and search file.
• All the files will be saved in the same path
• All are excel files
• Few files may have multiple sheets
• Files will keep increasing by one every month
Hope you can help me with this

See: venkatkumar1111_Search.xlsm
Place this file with the other files in a folder.
Click on search button.

I placed the results in a msgbox.

But you can place the results in your worksheet. (It wasn't clear to me where you wanted this information placed.)

' Collects the names of all Excel Files in Path (except for this workbook)
Function GetExcelFilesInFolder(sPath As String) As String
    Dim sFile As String, sList As String, sThisFile As String
    'The name of this workbook is excluded from the list
    If sPath = ThisWorkbook.Path Then sThisFile = ThisWorkbook.Name
    sFile = Dir(sPath & "\*.xls")
    Do While sFile <> ""
        If sFile <> sThisFile Then sList = sList & sFile & vbCr
        sFile = Dir()
    Loop
    If Len(sList) Then sList = Left$(sList, Len(sList) - 1)
    GetExcelFilesInFolder = sList
End Function

' Looks for a string in every Excel file in path
' Returns the row where string was found
Function Search4String(sString As String) As Variant
    Dim sList As String
    Dim sArray() As String
    Dim i As Long, j As Long
    Dim wb As Workbook
    Dim rgCell As Range
    Dim nLastCol As Long
    
    sList = GetExcelFilesInFolder(ThisWorkbook.Path)
    sArray = Split(sList, vbCr)
    For i = LBound(sArray) To UBound(sArray)
        Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & sArray(i))
        If Not wb Is Nothing Then
            For j = 1 To wb.Worksheets.Count
                With wb.Worksheets(j)
                    Set rgCell = .Cells.Find(What:=sString, _
                           LookIn:=xlValues, _
                           Lookat:=xlWhole, _
                           SearchOrder:=xlByRows, _
                           SearchDirection:=xlNext, _
                           MatchCase:=True)
                    If Not rgCell Is Nothing Then
                        nLastCol = .Cells(rgCell.Row, .Columns.Count).End(xlToLeft).Column
                        Search4String = .Range(.Cells(rgCell.Row, "A"), .Cells(rgCell.Row, nLastCol))
                        wb.Close
                        Exit Function
                    End If
                End With
            Next j
            wb.Close
        End If
    Next i
End Function

Sub Run_Search()
    Dim vRow As Variant, i As Long, str As String
    
    Application.ScreenUpdating = False
    vRow = Search4String("User smb 4")
    For i = LBound(vRow, 2) To UBound(vRow, 2)
        str = str & vRow(1, i) & vbCr
    Next i
    Application.ScreenUpdating = True
    MsgBox str
End Sub

Thanks a lot Steven for your solution....!!

Its working perfectly fine ...

Could you please help me to modify it a bit..


I created a form . I want to get the form up when we click on search
When we input the data in the form and select "find"
We should be able to get the results in the bottom box of the form.


Attached is the updated folder.

Could you please help me with this...

Thanks in advance
Venkat