从不同的工作簿搜索

我有一个工作簿，其中包含多张数据表（一张按品牌）。我需要从其他工作簿中进行搜索并获得所有结果。

此代码有效，但仅当在同一工作簿中搜索时才有效。

有人知道我如何修改我的代码来做到这一点吗？

Sub Search_With_Multiple_Results()

Dim Results As String
Dim Sheet As Worksheet
Dim Found
Dim FirstAddress
Dim r

r = 5

 For Each Sheet In ThisWorkbook.Worksheets
    Set Found = Nothing
    With Sheet.UsedRange
        Set Found = .Cells.Find(What:="Value searched", LookIn:=xlValues, LookAt:=xlPart,  SearchOrder:=xlRows, SearchDirection:=xlNext, MatchCase:=False)
        If Not Found Is Nothing Then
            FirstAddress = Found.Address
            Do
                If Results = vbNullString Then
                    r = r + 1: Results = "Worksheet(" & Sheet.Index & ").Range(" & Chr(34) & 
Found.Address & Chr(34) & ")"
                    Cells(r, 7).Value = Results
                Else
                    Results = Results & "|" & "Worksheet(" & Sheet.Index & ").Range(" & Chr(34) &  Found.Address & Chr(34) & ")"
                        r = r + 1: Results = "Worksheet(" & Sheet.Index & ").Range(" & Chr(34) & 
Found.Address & Chr(34) & ")"
                        Cells(r, 7).Value = Results
                End If
                Set Found = .FindNext(Found)
            Loop While Not Found Is Nothing And Found.Address <> FirstAddress
        End If
    End With
Next
    If FirstAddress = Empty Then
        MsgBox ("Not found")
    End If
End Sub