Need to find date from one sheet and paste in 2nd sheet

[punna111]

Hi all,

I have some data on sheet 1 with date and order no. i need to find only one certain date results, and i need to paste those in 2nd sheet. here we can't use filter because these dates are not in a single column these are in multiple columns. please help me....

[JBeaucaire]

You can use the FIND method to search the entire worksheet for the first instance of date, memorize that position as the "first", then start to build an array of all the rows that have that date, each time jumping with the FIND command until we find the "first" again. Then stop, then copy all the rows at once to a new sheet.

Please Login or Register  to view this content.

Dates are tricky, notice in the FIND command I formatted the date to a specific display format? The FIND needs to match the date as it is displayed on your sheet, so I hope you're consistent in that regard. Edit that part to the format that matches your display of dates.

[JBeaucaire]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Thanks.

[punna111]

Hi JBeaucaire,

Sorry for late reply, I was busy with other work. Thank you very much for your reply.
I impressed a lot on the above code. My last question.

in B column i will have status like "required" and "not required" I need only "required" data to be searched and copied in sheet2. what shall i add in code? Could you please advise me.

Thanks once again.

[JBeaucaire]

I've added that into the code above, try it again.

[punna111]

Thanks a lot JBeaucaire,

I appreciate all your help..

[punna111]

Hi JBeaucaire,

I attached the excel file.

The code you have given is not working. Could you please check...
Sorry for asking again...

[JBeaucaire]

My apologies, the restructuring to add in your new requirement was in error. Should work now.

NOTE: the date 7-Jun-13 is in the wrong format and will not be found. Only dates displaying in D-MMM format will be found. This is a TEXT match on the date display.