Copy row of any cell with date in the past and paste to new sheet

I need some help with creating a VBA that will scan my entire sheet and any cell with a date before todays date, the entire row will be copied and pasted to another sheet. and it should search every cell in sheet 1 and paste all rows with dates in the past. if there is more then one cell in a row with the date in the past, that row will copied only once.

Maybe:

Please Login or Register  to view this content.

Hey JOHN,
I tried that code and for some reason it goes from the bottom of the sheet to the top. i would want something that goes top down. and for some reason it is moving almost all rows

Hi Jerry: Performance-wise you have a much better solution. My method loops each row and then for each cell in that row "1 to columns.count", if it finds a cell < date it copies that cells row and goes too the next row. Multiple cells in the same row would be avoided with the Goto command, wouldn't it?

Originally Posted by jeetamin

Hey JOHN,
I tried that code and for some reason it goes from the bottom of the sheet to the top. i would want something that goes top down. and for some reason it is moving almost all rows

Did you try Jerry's code? Maybe it will work for you. Otherwise, please attach a sample file with some data to test on.

JBeaucaire,
Your code gives me an error saying that there is a type mismatch..

here is a sample file...i need the rows with dates in the past to be pasted to new sheet...

This copies to Sheet2.

Please Login or Register  to view this content.

the problem that is arising is that in the master file there are cells with other values in it..such as prices which is messing up the code. is there a way to only do the scan on certain selected files within the sheet?

You can incorporate IsDate as Jerry did in his solution.

Please Login or Register  to view this content.

Thanks guys! that seems to work. Now if I wanted to add a condition such as if there was a cell that said "Q M" then that row should also be pasted to new sheet how would i go about that? again i dont want a row to be pasted twice in sheet 2.

this doesn't seem to be working..is there any way to add it to JOHN H. DAVIS's code?

Maybe:

Please Login or Register  to view this content.

when I try that JOHN I get error message "Type Mismatch"

Originally Posted by jeetamin

when I try that JOHN I get error message "Type Mismatch"

I can't duplicate the error with your previous attachment. But try:

Please Login or Register  to view this content.

That works but if there is a row with a Q M and a late date it will pull the row twice. is there a way so that the rows are not duplicated?

Try:

Please Login or Register  to view this content.

Works like a charm! Thank you for all your help!!

You're welcome. Glad to help out and thanks for the feedback. Please comply with Forum Rule No. 9.

9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.