I was wondering if anyone can help with a macro that would search for a value entered in a dialog box within a number of worksheets and then place the result in another worksheet.
I have a number of worksheets that each have date values in their respective columns A. The dates in column A of each sheet might exist multiple time with column A (i.e. today's date might show up in a few places in column A of sheet 1). The same is true for all other sheets.
What I would like to do is have a macro that will search every sheet for a certain date that I enter in a dialog box, copy a range of the row where the date was found at and place everything (from all the worksheets) in a separate worksheet. For example, if I enter Jan 1, 2011 in the dialog box after running the macro, the macro should search every worksheet at columns A, find Jan 1, 2011 entries, and then copies a range (let's say A1:A6) from the rows that Jan 1, 2011 was found at and put everything in a separate worksheet.
The result should be a number of rows having Jan 1, 2011 in column A and corresponding data in range A2:A6.
This is how I would approach
1. obtain the date to be searched
2. loop thru each sheet
3. test if the sheet being looped is not the sheet where the results are to be copied. If yes, then loop to next search
4. filter each sheet for the date entered
5. if number of visible row is more than 1, then copy the visible rows and paste to the sheet