Maybe this could help. This would only look for the last instance of the
string you are looking for amongst the sheets. You can easily modify to
run thru all instances of search string to get the range required.

Option Explicit

Sub FindName()
Dim found  As Range
Dim ws As Worksheet, LookFor As String
LookFor = InputBox("Enter name to find")
If LookFor = "" Then Exit Sub
For Each ws In ActiveWorkbook.Worksheets
Set Found = Cells.Find(what:=LookFor, LookIn:=xlValues)
If Found Is Nothing Then Exit For
Next ws

If Found Is Nothing Then
MsgBox LookFor & " not found.", vbExclamation
Else
Found.Activate
End If

End Sub