PDA

View Full Version : Search Worksheet for Name if found copy to new workbook



richardSmith
10-11-2012, 05:48 AM
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
10-11-2012, 06:20 AM
It's possible but you have to attach some sample file

richardSmith
10-11-2012, 06:40 AM
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
10-12-2012, 05:43 AM
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
10-12-2012, 06:19 PM
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
10-13-2012, 06:55 PM
To clear the sheet whenever you run this SUB, after the line "Set sh = ...." add
sh.Cells.ClearContents

richardSmith
10-15-2012, 08:18 AM
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
10-15-2012, 10:32 AM
in wich folder you have the files ? did you see this line ?
mFolder = "C:\test\" ' <<<<<<<<< only search workbooks here

richardSmith
10-15-2012, 12:42 PM
Yes, I modified that path to

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

richardSmith
10-18-2012, 05:30 AM
BUMP ---- Any words of wisdom on what could rectify this?

patel
10-18-2012, 06:15 AM
The code works on my testfile, attach again your file with new code

richardSmith
10-18-2012, 06:56 AM
The code works on my testfile, attach again your file with new code

Attached is the workbook with code I use

patel
10-18-2012, 09:28 AM
maybe your file is damaged, try this attached