Search Worksheet for Name if found copy to new workbook

[richardSmith]

I have 5 separate workbooks that contain lists of employee first names, and employee last names(as well as phone numbers emails etc). I want to open my workbook that contains an input box, enter an employee first name and have vba search the 5 workbooks, and if found (even a partial match, like Mitch and Mitchell) copy the entire row to the workbook that I have my input box.

Is this an attainable function for excel vba to do?

[patel]

It's possible but you have to attach some sample file

[richardSmith]

Sure thing...attached are a few garbage data workbooks(of course the actual workbooks will contain many many more rows, but this should give ya a good feel of what I want to accomplish)

[patel]

Public Sub InputBox()
Dim EName As String
Set sh = ThisWorkbook.Sheets(1)
LR = sh.Cells(Rows.Count, "A").End(xlUp).Row
EName = Application.InputBox(Prompt:="Please Enter the Employee's First Name to Search For.", Title:="Employee Search")
if EName = "" then exit sub
Dim strFile As String
mFolder = "C:\test\" ' <<<<<<<<< only search workbooks here
strFile = Dir(mFolder & "*.xls*")
Application.ScreenUpdating = False
Do While strFile <> ""
Workbooks.Open mFolder & strFile
Set Rng = ActiveSheet.UsedRange
Set c = Rng.Find(EName, LookIn:=xlValues)
If Not c Is Nothing Then
Rows(c.Row).Copy sh.Cells(LR, 1)
ActiveWorkbook.Close True
Exit Do
End If
ActiveWorkbook.Close True
strFile = Dir
Loop
Application.ScreenUpdating = True
End Sub

[richardSmith]

I just thought about one more feature (if possible) to have added. Once the results from the 1st search are returned to the worksheet, would I be able to clear the worksheet of the 1st search results then (once added) push a button on the worksheet to launch the search form to perform a subsequent search?


Thank you for that code btw, I will test and provide feedback :)

[Teeroy]

To clear the sheet whenever you run this SUB, after the line "Set sh = ...." add

sh.Cells.ClearContents

[richardSmith]

Public Sub InputBox()
Dim EName As String
Set sh = ThisWorkbook.Sheets(1)
LR = sh.Cells(Rows.Count, "A").End(xlUp).Row
EName = Application.InputBox(Prompt:="Please Enter the Employee's First Name to Search For.", Title:="Employee Search")
if EName = "" then exit sub
Dim strFile As String
mFolder = "C:\test\" ' <<<<<<<<< only search workbooks here
strFile = Dir(mFolder & "*.xls*")
Application.ScreenUpdating = False
Do While strFile <> ""
Workbooks.Open mFolder & strFile
Set Rng = ActiveSheet.UsedRange
Set c = Rng.Find(EName, LookIn:=xlValues)
If Not c Is Nothing Then
Rows(c.Row).Copy sh.Cells(LR, 1)
ActiveWorkbook.Close True
Exit Do
End If
ActiveWorkbook.Close True
strFile = Dir
Loop
Application.ScreenUpdating = True
End Sub

The code is not opening any of the other workbooks. mFolder is returning a value but strFile is not. If I use debug.print and step thro the code strFile remains null.

[patel]

in which folder you have the files ? did you see this line ?
mFolder = "C:\test" ' <<<<<<<<< only search workbooks here

[richardSmith]

Yes, I modified that path to


mFolder = "C:\Users\Administrator\Documents\Testing\Excel\Templates\"

[richardSmith]

BUMP ---- Any words of wisdom on what could rectify this?

[patel]

The code works on my testfile, attach again your file with new code

[richardSmith]

The code works on my testfile, attach again your file with new code

Attached is the workbook with code I use

[patel]

maybe your file is damaged, try this attached