Help, I can't figure this out

[sharpie23]

I posted earlier but no one responded, did no one look at it, or does
no one know how to solve it?
Here is my question,
I am trying to write a Sub that will lookup the 36-40 file names on
sheet(2) column E of my MAIN wrkbook. It will then go into each of the
36-40 wrkbooks , sheet(1) of each wrkbook, and search down column K.
Everywhere there is a "Q" in a column it will copy all info in that row

(from column A to H) and then paste that info on Sheet(3) of the MAIN
wrkbook. Obviously each new paste will need to be placed at the first
empty row.

Any advise would be awesome.

Thanks,
Ryan

 

[hideki]

I'm sorry because I'm not fully understood your question (due to my wea
English).

Does 36-40 means from row 36 to 40?
Does the other workbook are in the same folder/directory with mai
workbook?

BTW here is a sample code that open a workbook (written on the ro
36-40 in column E) and look at column K in first sheet of the newl
opened workbook. If it found "Q" in that column, it will write fro
column A to H of the row to the Main workbook. Is this is what yo
wanted?

This sample may not conform 100% with your requirement but may be i
will get you started. For example, here I only look from row 1 to 10 i
the column K of the newly opened workbook. You have to alter to sui
yours.

Sub CopyFromOtherWB()

Dim strFileName As String
Dim lngRow As Long
Dim lngKRow As Long
Dim wbMain As Workbook
Dim lngSRow As Long

Set wbMain = ThisWorkbook
lngSRow = 1

For lngRow = 36 To 40
If Cells(lngRow, "E").Value <> "" Then
strFileName = Cells(lngRow, "E").Value
Workbooks.Open strFileName
With ActiveWorkbook.Sheets(1)
For lngKRow = 1 To 10
If Cells(lngKRow, "K").Value = "Q" Then
.Range(.Cells(lngKRow, "A"), .Cells(lngKRow
"H")).Copy _
Destination:=wbMain.Sheets(3).Cells(lngSRow
"A")
lngSRow = lngSRow + 1
End If
Next
End With
Workbooks(strFileName).Close savechanges:=False
End If
Next

MsgBox "Finish"

End Sub

Good luck,
hidek