autocopy rows from one spreadsheet to another depending on the value of a cell in each row

Welcome to the forum

from the looks of it, you probably need a VBA solution, but, you have not provided the needed information for any usable answer, FIRST>which cell in AB determines copy?
otherwise, this is all guesswork that is almost guaranteed to NOT meet your needs

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Okay, have uploaded 3 examples for you (all in the same wb, just separate sheets):
Example 1)
In a2 of cd:

Formula:

Please Login or Register  to view this content.

Drag down and across
This works well for data set provided, however as the amount of data grows, this will start taking longer and longer to process (all the solutions will, of course, but this one in particular )
this assumes all column headers will be in same position on cd sheet as in ab sheet

Example 2)
In a2 of cd (2):

Formula:

Please Login or Register  to view this content.

Drag down
In b2 of cd (2):

Formula:

Please Login or Register  to view this content.

Drag down and across
This will work better for larger data sets
Assumes all columns in same position as ab sheet and that MNP No. (column a) is unique to each product

Example 3)
In a2 of cd (3):

Formula:

Please Login or Register  to view this content.

Drag down
In b2 of cd (3):

Formula:

Please Login or Register  to view this content.

Drag down and across
Similar to example 2, it assumes MNP No. is unique to each product, however, it does not assume column position is constant, and is bettr suited to a summary page where some column data may not be required, or the order of columns may be different than on sheet ab

Hope this helps

Umm...which one?... lol
Glad you are happy!
and you are welcome!