Is there a way to search multiple workbooks in a file with user input for
search then copying the data to another worksheet? Thank you in advance for
any help.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200703/1

Something like this should work:
Sub SearchWorkbook()
Dim ws As Worksheet
Dim r As Range
Dim sSV As String 'Search Value
sSV = InputBox("Please Enter Search Value", "Searching...")
For Each ws In ActiveWorkbook.Worksheets
Set r = Nothing
Set r = ws.Cells.Find(What:=sSV, _
After:=ws.Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)
'Change LookAt:=xlWhole to xlPart if you want a partial match
If not r Is Nothing Then r.Copy 'Your Destination
Next
--
Charles Chickering

"A good example is twice the value of good advice."


"DJTI via OfficeKB.com" wrote:

Is there a way to search multiple workbooks in a file with user input for
search then copying the data to another worksheet? Thank you in advance for
any help.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200703/1

Thank you Charles. I will take your advice and work with it.
Deb

Charles Chickering wrote:
Something like this should work:
Sub SearchWorkbook()
Dim ws As Worksheet
Dim r As Range
Dim sSV As String 'Search Value
sSV = InputBox("Please Enter Search Value", "Searching...")
For Each ws In ActiveWorkbook.Worksheets
Set r = Nothing
Set r = ws.Cells.Find(What:=sSV, _
After:=ws.Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)
'Change LookAt:=xlWhole to xlPart if you want a partial match
If not r Is Nothing Then r.Copy 'Your Destination
Next
Is there a way to search multiple workbooks in a file with user input for
search then copying the data to another worksheet? Thank you in advance for
any help.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200703/1