Microsoft Excel - Macro for search variable, value or date if found copy to new workbook

To whom it may concern

Good Day

I have an excel work book with 10 sheets. In that work book 1 to 5 rows are header for all sheets. I would like to search a Value, or a Variable or Date as I required. If it found then entire rows(all details) should copy to a new work book. I need button for run the macro. Program should ask what I need to search for. If I put a date macro should search all workbook if found all result should copy to a new workbook. Can any one give a solution for this.

open a new file and save it. I have named the file as "master".  keep this file also open(it is easier)

The macro to be run is "test".  the other macro "undo" to clear the data from sheet1 of fiel "master" for rechecking
when the macro "test" is run the file in which the macro is parked should be the active file and NOT "master" file
if there is a bug or problem in running the macro "test" you have to give the code statement which gives problem and also error message if any. In my excel vesion(excel 2002) and my regional configuration the date is ENTERED  as mm/dd/yy.

the two macros are

Sub test()
Dim r As Range, cfind As Range, add As String
Dim j As Long, k As Long, x As String, dest As Range
x = InputBox("type the value or date")
If IsDate(x) Then x = DateValue(x)

j = Worksheets.Count
For k = 1 To j
With Worksheets(k)
Set cfind = .Cells.Find(what:=x, lookat:=xlWhole, LookIn:=xlValues)
If Not cfind Is Nothing Then
add = cfind.Address
MsgBox cfind.Address
cfind.EntireRow.Copy
End If
With Workbooks("master.xls").Worksheets("sheet1")
Set dest = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
dest.PasteSpecial
End With
Do
Set cfind = .Cells.FindNext(cfind)
If cfind Is Nothing Then Exit Do
If cfind.Address = add Then Exit Do
cfind.EntireRow.Copy
With Workbooks("master.xls").Worksheets("sheet1")
  Set dest = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
dest.PasteSpecial
End With
Loop
End With 'worksheets(k)
Next k
End Sub

Sub undo()
With Workbooks("master.xls").Worksheets("sheet1")
.Cells.Clear
End With
End Sub

in my macro there is a statement

msgbox cfind.address

change it to

'msgbox cfind.address

(that is put a single apostrophe in the beginning of the line so that it is inoperable)

I opend two files one test.xls and one Master.xls. In the text file I copy and pate the Macro which you given. Also I put 'msgbox cfind.address. Then I type some values 145,175 in column B and column C of the master file and save. I opend the test file and run the macro it asked to value or date. I type 145 for search. But it shows run time error '5 and invalid procedure call or argument show. If we clik cancel we can see Set cfind = .Cells.FindNext(cfind) have copied on the master file. if we select debug then the same line shows in the macro. Hoping you will a clarification. 

Can you explain both macro put in test.xls ?

in excel you should type entries carefully. 

Hi Venkat,

Have tried your macro. It has worked to an extent wherein the value which i am searching is in every sheet. However, I am stuck with a problem where the searched value is not in all the sheets.. Example : I am searching value "6099" in 12 sheets in all and only 6 sheets have value "6099".

Your macro stops as soon as sheet does not contain the desired value. It does not go on to seach in the rest of the sheet. I require that the sheet not containing the desired value gets skipped and next sheet gets searched.

Request you to help. Thanks in advance

New Folder (2).zip I attached the zip file that contains your macro can you correct it and send to me?

New Folder (2).zip I attached the zip file that contains your macro can you correct it and send to me?

New Folder (2).zip I attached the 2 files which contains your macro can you correct it and send to me?

New Folder (2).zip can you correct it and send to me

this is a bit confusing. my thread was apriol 2010.  your reply is jan 2011.  beides your zip files do not have DATA but only the macro. suggest o;pen a new thread and explain and attach a file with data and macro.