Excel macro question [Closed]

Report
Posts
6
Registration date
Thursday December 8, 2011
Status
Member
Last seen
February 3, 2013
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
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.

2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
767
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
Posts
6
Registration date
Thursday December 8, 2011
Status
Member
Last seen
February 3, 2013

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?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
767
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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!