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
Formula: copy to clipboard 
=IF(ReportWorkCopy!A2="SSN",ReportWorkCopy!A1,"")
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.