Finding Excel Data

Not sure if I understand exactly, but here is a starting point.  It looks for the text you mentioned and copies what's below that to Sheet4.  Try running it and see if it gives the results you wanted.  If not please specify further details.  Example test workbook attached.

Sub FindAndCopy()
    With ActiveSheet
        Set c = .Cells.Find(What:="6. File Number", After:=.Range("A1"), LookIn:= _
            xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
            xlNext, MatchCase:=False, SearchFormat:=False)
        If Not c Is Nothing Then
            .Range(.Cells(2, c.Column), .Cells(.Cells(.Rows.Count, c.Column).End(xlUp).Row, c.Column)).Copy Sheets("Sheet4").Range("A1")
        End If
    End With
    
    Set c = Nothing
    
    With ActiveSheet
        Set c = .Cells.Find(What:="Address of Borrower", After:=.Range("A1"), LookIn:= _
            xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
            xlNext, MatchCase:=False, SearchFormat:=False)
        If Not c Is Nothing Then
            .Range(.Cells(2, c.Column), .Cells(.Cells(.Rows.Count, c.Column).End(xlUp).Row, c.Column)).Copy Sheets("Sheet4").Range("B1")
        End If
    End With
End Sub

Select allOpen in new window

EE29141288.xlsm

---

»bp

That didnt work, I have attached a sample form.  Basically everything in Bold, I need to be in individual cell son the next sheet.
sample12.xlsx

Well that is a much more complicated set of data to manipulate.  I think it will involve searching for each of the merged cells that you want data from and copying them one at a time.

For this example can you show what the desired result should be on the next sheet?

---

»bp

I have attached what the results should look like.

sample12.xlsx

Okay, here is an approach.  I created a small function and used it on your sample sheet.

EE29141288.xlsm

---

»bp

Bill I ran it, so I cant get the data for "101.  Contract sales price" and beyond it just give me a #value error

Okay, you hadn't listed those in the most recent example of the output you wanted.  But you are back to needing all bold items I assume?

Those aren't working because the data is in a different cell than the search text, so I'll have to add some logic and parms to handle that.  I'll be back...

---

»bp

Awesome, thanks Bill, i really appreiciate all the work on this, your a life saver

Okay, this seems to be working here.  Some of the ColumnOffset values passed to the GetNamedData() function took a little trial and error due to the heavy usage of merged cells, but I think I got the data you wanted.  The beauty of this approach is the one function can gather all the needed data based on related labels, either in the same cell as the data, or in a nearby cell.  So if you have additional needs down the road it can still be used by just sending it the proper parms.

EE29141288.xlsm

---

»bp