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

Asked By John on 14-Apr-10 08:26 AM


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.
R VENKAT replied to John on 15-Apr-10 11:53 PM
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


R VENKAT replied to R VENKAT on 15-Apr-10 11:59 PM
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)
John replied to R VENKAT on 17-Apr-10 01:51 AM

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. 
John replied to John on 17-Apr-10 02:02 AM

Can you explain both macro put in test.xls ?
R VENKAT replied to John on 17-Apr-10 11:11 PM
in excel you should type entries carefully. 
you must type 145175 (without a comma). you can format it with a comma.  when you format this entry with comma in thousands place it LOOKS like 145,175 but the entry remains 145175.  so when you use "find" function and put is as 154175 it will find. 
similarly depends upon regional setup of excel the dates should be entered as mm/dd/yy.

regarding the second question of explaining the macro it is difficult. One way of understanding is 
keep both the spreadsheet and the macro on both sides on the screen(by reducing them). now keep the cursor within the macro and
hit F8 successively.  with the first hit "sub test" will be highlighted in color yellow .for each hit of F8 the yellow stripwill move to the next statement. with each hit see what change takes place on the spread sheet and that is how you can understand the macro.  generally this macaro  finds out wherever the input  value is, and if  found  it copied to the master sheet. it loops through all the cells in each sheet and loops through all the sheets. 

According to the macro if the entry is 145175 input box should contain 145175.  but if you want to put 145 in inputbox and the macro to find 145175 some modification has to be done .  In the macro there is a line 

Set cfind = .Cells.Find(what:=x, lookat:=xlWhole, LookIn:=xlValues


in this see "lookat:=xlwhole"   so that it will find the whole entry

if you want to find part of the entry (145)
REPLACE "XLWHOLE" BY "XLPART".

It all depends upon how far is  your familiarity\with WRITING  a macro;.   If you are a beginner don't attempt now. learn macro and then you can become an expert.   Best way to learn writing macros is suppose you have a small pronblem RECORD the macro taking all the steps and see the macro.   you can understand the various statements and can modify it to be more general .  If you are beginner both for excel and edcelvba get good books on excel(your version) and also on excel programming(your version).  These books will be a useful guide  provided you use excel extensively..

Alok replied to John on 15-Jan-11 02:20 AM
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
John replied to John on 16-Jan-11 04:30 AM
New Folder (2).zip I attached the zip file that contains your macro can you correct it and send to me?
John replied to John on 16-Jan-11 04:30 AM
New Folder (2).zip I attached the zip file that contains your macro can you correct it and send to me?
John replied to John on 16-Jan-11 04:46 AM
New Folder (2).zip I attached the 2 files which contains your macro can you correct it and send to me?
John replied to R VENKAT on 16-Jan-11 04:49 AM
New Folder (2).zip can you correct it and send to me
R VENKAT replied to John on 16-Jan-11 09:08 PM
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.