Paste in Different Worksheet, and is there a better method?

[OpieWinston]

Two Questions

I have data in columns A through M on worksheet "DATA", with the data in columns C and D being either YES or NO. What I'd like to happen is if there is a YES in either column C or D, I want it copy all columns in that row and paste it on the next available line on the next worksheet called "Past Due Report".

What has me stumped is how to get it to paste in the next available line on the Past Due Report sheet. I can get it to paste in a specific line, but I want it to paste in the next available line. So it might be row 100 on the "DATA" sheet, but row 25 on the "Past Due Report" sheet for example.

Would someone please show me how to do this?

Second question:

I planned to do this by

Please Login or Register  to view this content.

with the active cell being in Column C and moving down after every check. My sheet typically has a couple thousand rows that it'd be checking. Is there a better way to do this, or is the if code the best?

[mehmetcik]

1. Use this formula:-

Please Login or Register  to view this content.

The 3 tells the macro to find the last used cell in column 3 and add 1

2. Your Solution will require 2000 cells to be checked so probably very Slow.

The fastest solution would probably be to use a helper column with the formula

Please Login or Register  to view this content.

So you can use something like this:-

Please Login or Register  to view this content.

[Akuini]

Here’s another way:
I assumed:
-Data start at row 2
-Last column is col E, you may change this.

Please Login or Register  to view this content.

[Akuini]

Sorry, I just realized that I misunderstood the requirement. I thought: if C = “YES” AND D = “YES”.
So, don’t bother with the above code, it won’t work.

[jolivanes]

Another possibility maybe

Please Login or Register  to view this content.

[OpieWinston]

I've not tried these yet, but will tomorrow, and assume that one of them will work for my needs. Thank you folks!