Help cleaning up a macro

Thanks for the help. I'll try to explain it a bit better. I can't upload the exact stuff I'm working on because it contains private info, but I've uploaded a dummy sheet that should help with the explanation.

I want the macro to search in column BI for any blank cells or for any cells containing a 1. I then want those entire rows to be copied into a different worksheet so only those records are available. I know I can do this by autofilter, but I was asked to come up with a way to automate the process.

So, just for clarity, in the example that I have provided, I want the macro to locate the blanks and 1's in column BI (the first two rows), and then select those entire rows and paste them into the second worksheet.

Thanks for the help!

royUK, I looked at the code you directed me to, but couldn't figure much out from it. Does that code tell the sheet to delete rows? If so, I don't want any rows to be deleted. Just copied and pasted into a new workbook.

Mordred, I tried the code you suggested, but it comes up with an error saying the subscript is out of range.

Thanks for the quick responses.

Hi gugg7378,

I just tried Mordred's code on the workbook you posted and it works. What error are you getting and is it on this workbook or another?

abousetta

Sorry, stupid mistake. I had one of my worksheets named wrong in a different workbook, which was causing the issue. Thanks a ton, it works great and MUCH faster than the way I originally had it!