Copy inconsistent data based on recurring cell reference & sequentially paste in new wksht

[Tbenefits]

Greetings - I'm new to VBA and have no idea how to begin to solve this issue; I have a report that I have to run monthly that is exported as a data only excel file (.csv isn't available). The spreadsheet varies in length each month, and the data I'm attempting to copy is separated at varying distances in Column A. The one constant is that "SSN" is always in the cell beneath the desired data. I currently have a formula

Please Login or Register  to view this content.

that serves my purpose, but I have to paste it each time all the way down the new worksheet, then sort and delete the blanks. I would like to be able to simply run a code that searches for the "SSN" on the report sheet, copy the cell above it, and then paste it in the new worksheet. I'm open to any solution, including if there's a formula that could do this, but I'm pretty sure this is a VBA issue... Thanks in advance for your help.

I've also attached a general example of the initial report (Report!) and the ideal result (ReportWorkCopy!). Or at least I'm attempting to... I'll go ahead and post this and see if I can't figure out how to attach my workbook.

[Tbenefits]

CopyPasteExample.xls
Ok - here is the example workbook. Please let me know if this isn't clear.

[Tbenefits]

bump no response

I'm new to forums, so if there's a reason this isn't getting answered, could someone let me know? Thanks again for your help.

[AB33]

Attached are two different codes. One is find which is faster than the loop code

[Tbenefits]

Fantastic! Thank you so much! They both work great! I do get a '424' error at

Please Login or Register  to view this content.

when I run the loop code, but I'm wondering if it's supposed to be that way as it produces the intended results before the error message?

Also, is there any advantage to using the loop code, even though it's slower?

I have some code that pastes formulas in adjacent columns and I'm going to see if I can insert this into your code, but if I can't I may be back to ask for some additional help. Thanks again! This is super helpful!

[AB33]

My bad

Please Login or Register  to view this content.

No, the loop would be slow, but slightly flexible than find. I just gave you two options.

[AB33]

Yes, the result would be correct. It just make the set zero after the code finished executing it.