Copy rows from Workbook 1 to Workbook2 if condition is met

If there is a formula or function to this outside of VBA or macro, I would rather go that way but I don't think there is. Even the VBA seems to be a doozy. I found similar questions but I cannot adapt the codes to my workbook for them to work properly, or at all.

In my Worksheet 1, currently named "Master" I have thousands of entries. When serial number in column A matches a serial number in another worksheet called "Reports", the date associated to the matched serial number in the "Report" worksheet is displayed in column P of the Master worksheet.

I now need to have all the rows that does not contain a date in column P of the Master worksheet to be copied in a separate worksheet. There is a received date column in the Master worksheet and the condition to copy the rows without a date in P needs to also include the rows with a date in P that is less recent than the date in D.

In other words, I need a worksheet to display all the rows from the Master worksheet except the rows with a date in P or a date in P that is later than the date in D.

It is not a one time procedure so I need the new Worksheet to be updated as the Master receives new entries or dates in column P are being populated.

Thanks again for the great help!

Here it is

Thanks, this is on the right track and useable. Is there any way to have it keep the source formatting? I reformatted the destination worksheet but as soon as I run the module again for an update, it goes back to a set format for the destination. Also, it seems like I have to run the module to have the worksheet updated with new entries or modifications from Master. If it's not possible to have it updated dynamically or automatically, how can I attribute a shortcut key for the module to run, i.e. Ctrl+2?

Thanks again so much!

Try
Change sheet names, if needed.

Please Login or Register  to view this content.

Thanks Jindon... xLAdept code was working a bit better. Yours does copy partial formatting for column A, but it only shows data up to row 61 in column A.

I need exactly what Xladept had done, but I need a shortcut key if it can't update automatically and I would need the source formatting to be copied as well, or to preserve the formatting that I will put in place for the destination worksheet. Currently, if I set all the formatting manually after I ran the module, all the formatting is reset if I run the module again for update.

Unfortunately, my sample workbook cannot be anything like my actual workbook. I'm also trying to have the refreshed Result worksheet to appear as the first tab instead of the last.
I suppose the following is the line to modify but I can't figure out how.

Please Login or Register  to view this content.

I also tried to play with the following code line, without success, in an attempt to keep the source formatting in regards to column width, however ideally I would like to keep all the formatting from the source, including cell size and fill colors, etc...

Please Login or Register  to view this content.

Also, I managed to associate the module to a short cut key using this:

Please Login or Register  to view this content.

So this is not an issue anymore. Most important thing would be to be able to keep formatting and column size from the source workbook, then have the tab populated at the beginning instead of at the end.

FANTASTIC! Simply wonderful! I wish I could understand that coding stuff because I would adapt this for many other usage.

I see where the conditions are coded and I'm trying to modify them to generate worksheet for other conditions. If I'd like to generate another worksheet, let's call it "Quick MEP" , that contains all rows showing "Quick Hits" in column I AS WELL AS "Yes | Oui" in column J, how would I code these conditions in replacement of:

Please Login or Register  to view this content.

After that I will try to figure this stuff out by myself. If I can understand how to input the conditions, I could use that coding to generate many other kinds of useful reports.

Many thanks again for the great help!

Thanks again,

I'm not able to add that code line to return the expected results. AS id it does not return anything expect the colors in the top rows. I changed the "Or" command by "And", and that gives me all the entries with Yes in column J regardless of what is in column I. It would be more important to retrieve the "Quick Hits" in column I so I tried with only the first part and it's not working. I also had to remove the End If because it was giving an error: End If without If block...

Here's what I tried:

Please Login or Register  to view this content.

This gives me all rows with Yes in J, regardless of Quick hits in I. Weirdly, it removed text from some of my header cells.

I also tried:

Please Login or Register  to view this content.

This gives me the exact same results as the previous code

And I tried this, which I thought would simplify things but does not return any entries:

Please Login or Register  to view this content.

Oh, and I also tried replacing the <> by = and it returned all the entries. Seems like I'm not getting that whole VBA thing... admittedly, the last programming I did was using Basic code on a commodore 64

Thanks for trying, but it provides the same results. I will quit on this idea as I feel like I'm abusing of your good will. I'll have to take on learning VBA coding in the near future

It boggles me how changing one argument in the code seems to have impact on everything else. i.e. in the Results code you gave me, all the formatting and the 2 top rows which serves as headers stays the same and is perfect. But in all the attempts for the MEP code, the text in the 2 top rows serving as headers is replaced by the actual data.

It simply does not make sense to me how modifying the If line to change the argument has an impact on my headers... anyhow... maybe it will make sense to me one day!

Thanks again for all the help!

Wish I could help, but I'm more at a loss than you are The only thing I can say is that I have both versions in 2 separate modules and I get the same Worksheet with the same entries regardless of which one I run. It pulls out 195 entries, so I could compare. It should actually pull out 18 out of the 195 that has a Yes in J. I have the count for how many has Yes in J and Quick hits in I, but I'm stuck at not being able to pull them from the master onto a different worksheet.

Sorry about the confusion. J does have "Yes | Oui"... I was using Yes as short for "Yes | Oui"

So, I managed to set things up in a workable way. First, although it doesn't logically make sense, I got the results I initially wanted (Yes in J and Quick hits in I) by using OR as the operator instead of AND. I know, does not make sense but it works.

I fixed half of the formatting issue by changing the first digit in the last line : ws.Cells(1, 1).Resize(s - 1, c) = Q to make it ws.Cells(3, 1).Resize(s - 1, c) = Q.
This made the entries to be copied starting at row 3 leaving the first 2 rows of headers empty in txt but colored and sized as the source worksheet. I'm loosing the text for the headers but I can live with that.

So I ended up making 4 worksheet generator codes in modules.

1- INVENTORY, To pull all rows with no dates in P or a date in P that is earlier than a date in P.
This works 100%, I get the headers' text copied as well, probably because the test picks it up in the lines to be copied. Here's the code:

Please Login or Register  to view this content.

2- MEP, Pulling the rows with "Yes | Oui" in column J. Works good, the only flaw is the headers' text does not get copied.
Here's the code:

Please Login or Register  to view this content.

3- QUICKHITS, Pulling all the rows with "Quick hits" in column I. Again, works good, the only flaw is the headers' text does not get copied.
Here's the code:

Please Login or Register  to view this content.

4- QUICKMEP, pulling rows with "Yes | Oui" in column JAND "Quick hits" in column I.... but using OR as a logical operator?!?!?!?
Here's the code:

Please Login or Register  to view this content.

Well thank ya! that was all I needed!