Sub test()
Dim wb As Workbook, sht As Worksheet
Set wb = Workbooks.Open("C:\Users\Administrator\Downloads\2.xls")
For i = 2 To ThisWorkbook.Sheets(1).Range("a65536").End(xlUp).Row
k = 0
For j = 1 To wb.Sheets.Count
If Not wb.Sheets(j).Cells.Find(ThisWorkbook.Sheets(1).Range("a" & i)) Is Nothing Then
k = k + 1
ThisWorkbook.Sheets(1).Range("b" & i) = wb.Sheets(j).Name
If k = Application.WorksheetFunction.CountIf(ThisWorkbook.Sheets(1).Range("a2:a" & i), ThisWorkbook.Sheets(1).Range("a" & i)) Then
Exit For
End If
End If
Next
Next
wb.Close
End Sub