Locate a set value and copy the value in the cell that's below it

[twmills]

Hello,

This is very similar to situation I received helped on recently. So I'm sorry in advance if this looks familiar to some of you. But I'm looking to search column A for the phrase "Account Number", then have it copy the actual number below it and paste in a separate sheet.

I provided an example spreadsheet of what the raw data appears like (Shareholders) and what the finished product should look like (Final). The heading "Account Number" on the Final sheet will all ready be provided, so that doesn't have to be carried over in the macro. The catch is, in some instances, there's a page break in the raw data preventing the actual numeric number to not be directly below the "Account Number" heading. I provided an example of this in lines 19-25 in the Shareholders tab.

Thanks as always.