Copy Data to new Workbook based on if/then

I need some help. I'm terrible at writing code, but I understand it enough to edit it to work for me.

Anyway, I'm at a loss. I can figure out how to copy to a new sheet, but I need a new workbook. I want to copy a row to a new workbook based on an if/then statement. Basically, if column I is occupied and column J is blank, then copy the whole row to new workbook (call it, Open PO Report.xlsx). Any pointers?


Thanks!

Maybe:

Please Login or Register  to view this content.

That was fast!

It works in theory. However, it only takes the last row of data, not every instance where the criteria isn't met.

Also, running that code made me realize a few things that I would additionally need. The data pulled from the workbook should go to an existing file (so it's properly formatted). And, the workbook the data is being pulled from is coming from three sheets.

It should pick up all the data where the criteria is met. At least it did when I tested it. We need to know the name of the existing file. Do you want the data from the three sheets consolidated into one in the existing file?

It's only grabbing the last row where the criteria is met. For example, the sheet I'm testing with has 4 rows filled out. 2 where the criteria is met (row 2 & 3). Only row 3 showing when the code is run.

Yes, I would want the data from the 3 sheets consolidated into the existing file.
Destination file = "Open PO Report.xlsx"

Can you attach a sample where it is not picking up?

Your answer:

Yes, I would want the data from the 3 sheets consolidated into the existing file.
Destination file = "Open PO Report.xlsx"

Do you want those 3 sheets consolidated into 1 sheet in the destination. or 3 separate sheets?

Both files are attached. Again, appreciate you taking the time to check this out for me!

Unfortunately, I have too run. If no one else helps. I'll see what I can do tomorrow.

Try:

Please Login or Register  to view this content.

Thanks for taking another shot! However, I got;

Run-time error '9'
Subscript out of range

Which line is highlighted?

Check the red too see if they exist.

Please Login or Register  to view this content.

Set ws2 = Workbooks("Open PO Report.xlsx").Sheets("Sheet1") <---is highlighted. Even put the full file path for the workbook and still had the same problem.

Is there a "Sheet1" ? Is the Capital "O" really a capital "O"? or a 0 (ie zero).

Yup, everything is there. Would it be easier to have the pulled info sent to another sheet instead?

What about the file extension? is it .xlsx? or .xlsm or other?

the extension is .xlsx