Previous Topic Next Topic

Excel macro question

Closed
nzstorm Posts 6 Registration date Thursday December 8, 2011 Status Member Last seen February 3, 2013 - Dec 8, 2011 at 08:53 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Dec 9, 2011 at 10:33 AM
Hi,

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.

Any guidance would be greatly appreciated.

Related:

2 responses

Answer 1 / 2
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Dec 9, 2011 at 05:49 AM
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
0
Answer 2 / 2
nzstorm Posts 6 Registration date Thursday December 8, 2011 Status Member Last seen February 3, 2013
Dec 9, 2011 at 09:05 AM
I should've mentioned that I am still a novice at writing macros. I can try to figure out how to do the first three steps. As far as step 4 and 5, what functions would you use to accomplish that?
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Dec 9, 2011 at 10:33 AM
Kind of surprised that you would find step 3 easier than step 4 :).
To see the code for filter, you could have used macro recorder. Only thing you have to keep an eye open is for date format. I think you would need to enter the date in same format as the date int he column for the filter to work

for item 5:
lLastUsedRow = cells(rows.count, "A").end(xlup).row

if (lLastRow > 1) then
' ok i have more than one row
end if
0

Similar discussions

Keyboard troubles
jimbobjoe -
car -

38 responses
Date formatting in dd/mm/yyyy
nisha -
appu -

11 responses