Sorry about the delay, senile dementia is my only excuse, but here is the code that I developed for searching a full workbook:
Sub findall()
'
'Macro created 11/01/2002 by Colin Robinson
'
On Error GoTo errortrap
response = InputBox("Please enter the word that you wish to search for.",
"Search"

For n = 1 To Worksheets.Count
Worksheets

.Activate
Set myrange = Worksheets

.Range("A1:AZ20"

myrange.Cells.Find(What:=response, After:=Range("A1"

,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
Do
response2 = MsgBox("If you wish to continue to search this sheet, click
'Yes'" _
+ Chr(13) + "If you wish to move to the next sheet, click 'No'" _
+ Chr(13) + "If you wish to cancel press, well, I'll let you guess that
one.", vbYesNoCancel, "Move on"

If response2 = vbCancel Then Exit Sub
If response2 = vbYes Then Cells.FindNext(After:=ActiveCell).Activate
Loop Until response2 = vbNo
backhere:
Next n
GoTo finale
errortrap:
MsgBox ("Nothing on this Sheet, Click OK to go to the next."

Resume backhere
finale:
End Sub
This provides the basics.
Add:
Application.screenupdating = false/true
Auto move to next sheet.
Loop thru the workbooks as well. I don't know if they're open or whether you're opening them to order.
Return activecell.address with the workbook and sheet name or number.
Get response from the cell that you want to search from. The reason it was bugging at that point in your code is cos you need sourcecell.value so you are only want to get the contents and not the entire object, (I think).
And that should do it. (it was a while ago I did this so no laughing)
PS I was referring to me when I said senile dementia, not my 86 yr old Gran who is getting to grips with her new computer nicely. The card games anyway, just cos she needs to practise with the mouse of course.