Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help, I can't figure this out
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help, I can't figure this out
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 -- hidek ----------------------------------------------------------------------- hideki's Profile: http://www.excelforum.com/member.php...fo&userid=1890 View this thread: http://www.excelforum.com/showthread.php?threadid=47995 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't figure this out... | Excel Discussion (Misc queries) | |||
HOW TO 'BLINK' A FIGURE INSTEAD OFF BOLDING THE FIGURE | Excel Worksheet Functions | |||
Pg 98 - Figure 3-24 Help | Excel Discussion (Misc queries) | |||
Can't Figure what I did | Excel Discussion (Misc queries) | |||
Can anyone help me figure this out? | Excel Programming |