am trying to load multiple workbook into the same sheet, these workbooks will all contain one common column header. After i load multiple workbook, i would like to search for the rows i wanted and paste it onto a new sheet.

so far i have done the search and paste part but the part that requires to load multiple workbook is quite tough, can anyone help me out on this? thanks.

Sub SearchRowAndCopy()
Dim strSearch 
strSearch = Application.InputBox("Please enter the search string")
x = 2
Do While Cells(x, 1) <> ""
If Cells(x, 2) Like "*" & strSearch & "*" Then
Worksheets("Sheet1").Rows(x).Copy
Worksheets("Sheet2").Activate
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("Sheet2").Rows(erow)
End If
Worksheets("Sheet1").Activate
x = x + 1
Loop
End Sub