*urgent help needed* copy data from one workbook to other workbook based on search value

[shahnawazatiq]

dear all,

i have one text box and a search (command) button in one excel workbook (search workbook) and my database is in other workbook (data workbook).my problem is that user has to write few words in text box provided in search workbook and press search button,on search button macro has been written to fetch data if it is found in data workbook.it should return those rows in the search workbook.kindly help me out
code written on search button is as follows .
****************************************
Private Sub Button2_Click()
Dim MyFiles As String
'code for open a workbook by shaan on 30-04-2010
' Specify a target directory
MyFiles = ActiveWorkbook.Path & "\*.xls"
'Open Workbooks one by one

Do While MyFiles <> ""
'Copy the Source Data
'Range("A1").Activate
If Sheet1.TextBox1.Text = "" Then
MsgBox ("Enter a search String")
Exit Sub
Workbooks.Open Filename:=MyFiles & "\RO_CIRCULARS.xls "
Workbooks.Open Filename:=MyFiles & "\excel_30apr.xls"
Else
Range("A1").Activate
If InStr(ActiveCell.Value, Sheet1.TextBox1.Text) Then
'Range(Selection, Selection.End(xlToLeft)).Select
'Range(Selection, Selection.End(xlDown)).Select
Range("A1:V400").Select
Selection.Copy
'Open the destination file

'Select next available row and paste copied data
Cells(Cells(Rows.Count, 1).End(xlUp).Row + 1, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'Close and Save the destination file
ActiveWorkbook.Save
ActiveWindow.Close
'Close the source file without saving
Workbooks(ActiveWorkbook.Name).Close SaveChanges:=False
'Next File in the Directory
MyFiles = Dir()
End If
End If
Loop
End Sub
'Private Sub CommandButton1_Click()
'If TextBox1.Text = "" Then
' MsgBox ("Select a Ingredient !")
' Else
' ListBox1.Clear
' Range("b1").Activate
' Do While ActiveCell.Value <> ""
' If InStr(ActiveCell.Value, TextBox1.Text) Then _
' ListBox1.AddItem ActiveCell.Offset(0, -1).Value
' ActiveCell.Offset(1, 0).Activate
' Loop
' End If
'End Sub

 

[somnath_it2006]

Go through this link - http://www.vbaexpress.com/kb/getarticle.php?kb_id=780