Auto complete from 1 sheet to another (can not repeat cells info)

Please guys... Any idea?!

Maybe that would work for You.

Please Login or Register  to view this content.

Thanks mate.
I will try tmrw at work and get back to you.

Hey guys.
First thanks to mumsys and Debraj Roy reply.
Unfortunately it didn't work. The error message was: "compile error: syntax error", and it was in the line Cell.EntireRow Copy orders.Range("A" & Rows.Count).End(xlUp).Offset(1)

Please find the Excel file below:
New production B.xlsx

Looks like we almost there, just a small error, right?


Well, looking forward to hear from you again

Btw, I am at work now so I can try and reply straight away

Thanks!

Hi guys!
Anybody else can help me with that?!
Cheers

I hate to be that guy that always put the topic up, but I really need some help on that
I will give a box of cookies to the person who help me :D

Please check if that work for You

Please Login or Register  to view this content.

Originally Posted by mumsys

Please check if that work for You

Please Login or Register  to view this content.

Wowww... almost there... the only problem is sometimes there is more than one item in a order, so the order number will repeat (some orders have up to 10 items)... For example order 6157 has 2 items:
ORDERS ITEM
6157 Item 21
6157 Item 49

There is a way to get the repeated order numbers as well???

Almost there buddy...
Thanks a lot!

Please Login or Register  to view this content.

Hi mumsys and other users,

So, the last macro fixed one point and created another problem.
Fixed that sometimes you have repeated orders with different items (it will have 2 cells in the same column with same data, but in another column it will have a different data).
The problem created is that the macro is copying including the cells that are already there. So every time I will run this macro, it will repeat everything I already have.

There is a way to fix it?
And the last point would be past values only (not formatting). Is that possible?

Excel file attache below describing all the points:
New production EXAMPLE.xlsx

Thanks guys!

What are trying to do?
Are you trying to compare(Match ) the master sheet with order sheet? Which column do you want to use compare both sheets?
You have SAP O/No. Item Description
on both sheets

Originally Posted by AB33

What are trying to do?
Are you trying to compare(Match ) the master sheet with order sheet? Which column do you want to use compare both sheets?
You have SAP O/No. Item Description
on both sheets

Hi AB33,

-What I am trying to do:
The "orders" sheet will be used by the production manager. The "SAP report" sheet will be updated everyday with the report from SAP (I will just extract as excel file and copy and paste on this file, in the "SAP report" sheet).
The production manager will run the macro and update the new orders in the "Orders" sheet.
Some orders can have more than 1 item, so the order number will be repeated in the sheet (SAP generate 1 item per row).

The point is, the orders that are already in "Orders" can not be modified, as the production manager will wright the production date and machine on those orders.
So the macro needs to update only what is not in "Orders" and is on "SAP report", including orders numbers that are repeated because have more than a item.

-Are you trying to compare(Match ) the master sheet with order sheet? Which column do you want to use compare both sheets?
Yes, I am trying to compare, and fill automatic. In both sheets they are in column A. From "SAP report" column A to "Orders" column A.
And it is just past value.


Sorry if I was too long.
I appreciate your reply.
Thank you!

Originally Posted by AB33

Okay!
All rows In sheet orders are in sheet master, but not all rows on sheet master are in sheet orders. That is, there are some rows missing from sheet order. All nos from 5922 in master sheet are missing in order sheet.
Do not stressed! We will find you a solution, just need some time to understand your data.

Cheers dude.
Looking forward to hear from you

Ric,
Please do not reply with quote, just a reply will do.
So, do you want to fill up sheet orders missing rows from master sheet? Do you want to copy column A only?
If it is the case, I will get back to you in 30 minutes, but need confirmation

The code brings all missing, including the duplicates. Try it and will amend if necessary.

Not a problem
Yes, I want to fill up cells from "SAP report" sheet column A, to "Orders" sheet column A.
Just wanna remember you that sometimes it has repeated cells that must be copied as well to "Orders", but the cells that are already in "Orders" cannot be edit or duplicated.

Thank you AB33!

Try post no 20.

Yes, it is working AB33.
Tomorrow (here is 10:30pm) I will try in the official sheet at work and get back to you ASAP.
I appreciate your effort and kindliness (and also your knowledge, I had a look in the macro and I was like ).
Thank you.

When you walk up in the morning, you might find out that the Aussie have already lost the "Ashes"

Sorry,
I did not realise the code was looping through the columns.
Use this code instead.

Please Login or Register  to view this content.

I reckon it is working, I did some quick tests and looks good... Thanks mate!
I will put this topic as "solved"... if I find some error I will let you know.
Have a good weekend

Hi AB33,

The macro stop if there is a blank cell. Could you please update to keep looping if it finds a blank cell?

You can find the sheet below explaining exactly what I mean:
Production example 2.xlsm

Thanks dude

The macro stop if there is a blank cell?
Not sure what do you mean? Do you mean you have a blank row or column so that the current region would not work?
The code will not loop until the last cell if there is a gap(Blank row or column).
On the attached there is none. You have 141 rows on both sheets, so need to clarify.

Hi AB33,
Thanks for the reply

What happen is the SAP report bring always 1 row with data and then 1 blank row, 1 row with data and then 1 blank row, and so forth.
I did the a new simulation so you can understand it better:
Production example 2b.xlsm

There is a way to fix the macro?!

If you didn't understand please let me know.
Thank you!

Okay, use this line

Please Login or Register  to view this content.

Instead of

Please Login or Register  to view this content.

Works perfectly
Thanks AB33.