Searching for text and displaying on another sheet...

sub FindString()
dim MyString as String
dim sh as worksheet
dim cell as range

On Error Resume Next
MyString ="Total Building Damages:"
For each sh in worksheets
Set cell = Range("A:A").Find(What:=MyString)
Next sh

Msgbox "Your string was found in: " & sh.Name & cell.address

'To get the offset into your report sheet:

Sheets("Report").Range("Whaterver")=cell.offset(0,6) '6 columns to the right

end sub

Hope this helps you get started