Need help in copying info when 2 critirias are met.

[pedie]

Hi,
I need a code that will run from book1 but copy info from book2.sheet1 if
2 critirias are met.
Critiria one = "Word1", This will located in col F so if word1 matches the word in col F then
sesize 3 and copy to book1.sheet1 "B".rows.count).end(xlup).offset(1)

Critiria 2 = book1.sheet1.range("R2").value
Data range to copy is from is book2, sheet1, row1 to row 1200 in sheet1.book1

book2 path = "D:\Macro Folder/Excel"

Thanks Pedie

 

[HalfAce]

ok, let's try this. Instead of looping through column O how about we just use the AutoFilter?
See if this is close...

Sub try12()
Dim MyFile As String

MyFile = "D:\Book2.xlsm"
On Error Resume Next
Set wb = Workbooks.Open(Filename:=MyFile)
On Error GoTo 0

If wb Is Nothing Then MsgBox "Error Occured, file not found": Exit Sub

With wb.Sheets("Sheet1")
  LstRw = Cells(.Rows.Count, "O").End(xlUp).Row
  .Range("O1:O" & LstRw).AutoFilter Field:=1, Criteria1:="Y"
  .Range("J2:N" & LstRw).SpecialCells(xlCellTypeVisible).Copy _
    ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp)(2)
  .AutoFilterMode = False
End With

ThisWorkbook.Activate

End Sub

If you want to loop through the column we can sure do that but this should be quicker.
Are we getting close with this?

 

[pedie]

Dan, sure.... I give it a try now. Please gimmi a moment

 

[HalfAce]

ok.
A moment's up. How'ed it work???

 

[pedie]

Dan, I am not sure why but it is only copying the heading row and nothing else in O col there is 2 Ys so what I think is that it will copy from 2 rows down but when i come back and check book1 it does not...I am not sure if am doing something wrong

 

[pedie]

Dan, now it is working..I didnt know what happened earlier..

Thanks a million. You did it

 

[HalfAce]

I saw a few loose ends that should be tied up but I don't know your particulars so I'm not sure just how they should be tied.

I'm glad you got it working.

(And my standard answer at work... "No, I didn't do it, you can't prove I did, and I'll never do it again!)

 

[pedie]

No, I mean Yes, you did it and you'll do it again!

 

[HalfAce]

and you'll do it again!

yeah... I probably will - but I won't admit it.

("Old enough to know better but still young enough to do it again!")