Need help in copying info when 2 critirias are met.

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
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;)
 
Last edited:

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
Sorry

Critiria 2 = book1.sheet1.range("R2").value...
this should be = book2, sheet1, col N

in a row in book2, sheet1,
If N col value = book1.sheet1 r2.value and
If F col value = "Word1" then
copy by resize(,3)
 
Last edited:

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
Peter, Brian...are you guys uys around???

I have changed my mind. If book2, O col has "Y" then copy the whole row and paste it in b col in book1.

Just one critiria...!;)
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456

ADVERTISEMENT

Hi Pedie,
I'm kinda confused as to your criteria now.
Can we start over with what you want to do so we can make it happen?
You want your code to be in book1 and copy from book2 (yes?)
You want to... what... loop through column O of book2, looking for a value of "Y" and then copy... what - (we cant copy the whole row from book2 and paste into column B of book1, we have to start in column A for a whole row).

Am I close?
 

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
Dan, thank God, you came to my resue, sure one moment.

Book1 is aleardy open, k?
When i run the code i want the code to look through col "O" in book2 then look for "Y" if found then offset -4 and resize 4 and copy it and paste it in book1 in range B.xlup offset 1
 

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875

ADVERTISEMENT

this could be funny but i want you to also see what i have got...lol
But this code does not work:biggrin:

Code:
Sub try12()
Dim wb As Workbook, MyFile As String
x = 1
y = 10000
c = 15
  
    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
    
    
    For chk = x To y
    Windows("Book2.xlsm").Activate
    Sheets("Sheet1").Select
      If Cells(chk, c).Value = "Y" Then
         Cells(chk, c).offset(0,-4).resize(0,4).copy
    Windows("Book1.xlsm").Activate
    Range("B" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveSheet.Paste
    End If
    Next chk
  End Sub
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
ok, book1 is open, do we want the code to open book2 for this? (if so, what's the whole path to book2?)
And do we want it to save & close book2 or anything at the end?
When you say if found then offset -4 and resize 4, do you want to offset 4 columns to the left, or 4 rows up - and same thing... want to resize 4 columns to the right or 4 rows down?
(And we're working with sheet1 of both books, yes?)

EDIT:
Oh, wait a sec. I didn't see your code. Let me go through it and see what you're doing...
 

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
Get the information but do not save and close book2
File path "D:\Book2.xlsm"
if found in O col, then it will copy from col J to N

Thanks again Dan!
 

Forum statistics

Threads
1,144,376
Messages
5,724,001
Members
422,530
Latest member
Badpoisondwarf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top