VBA code for store inventory transfers

[halimgunawan]

Experts,

I just found the old thread: https://www.excelforum.com/excel-for...ml#post4358551
and I need your help to edit the code from file attached into the format I need
Could anyone can edit the code for me for the file attached?
I have explained the expected result on file attached. Thanks

[halimgunawan]

Anyone can help?

[halimgunawan]

Could you pls help? Thx

[halimgunawan]

anyone can help?

[karmapala]

Maybe something like this ?

Please Login or Register  to view this content.

Tested only based on the data in your sample workbook.

2020-07-18_18-17-04.gif

I think the code can be shorter, but I have my headache to think about it more .

[halimgunawan]

@karmapala,

yes, thats what I need, but could you pls help to move the result to output sheet ? also could you pls add the field for color and size as well as attached file?

[karmapala]

@karmapala,

yes, thats what I need, but could you pls help to move the result to output sheet ?

but there is already data in your output sheet ?

also could you pls add the field for color and size as well as attached file?

How do I know that from shop bla to shop blu the color is ble and the size is blo ? .
I thought the value in column color and column size will be manually typed by the user after run the macro.

Ooops !
Sorry, just download your other attachment. LOL

[halimgunawan]

@Karmapala, the output sheet is the result of previous code, so you can erase it.

also for the color and size, I have added the information into new attached file above...

may I know if the coding will cover unlimited rows and columns? what I mean is if I add more products and shop, the coding will automatically calculate based on the updated data right?

[karmapala]

Like this ?

Please Login or Register  to view this content.

2020-07-18_18-50-43.gif

if I add more products and shop, the coding will automatically calculate based on the updated data

As long the added products and shop is always in a consecutive column/row yes the macro can still run.
The macro assumed that the column for shops will always start from cell D1 to the end right of the column which is the last shop. (please remember, no blank column in between).
The macro will stop the loop once it finds out that the range from cell D(n row) to cell LastColumn of the Shop (n row) has nothing inside it (empty), countA = 0.
The macro assumed that the total sum of the range from cell D(n row) to cell LastColumn of the Shop (n row) will always be zero / nol.
The macro must be run while on sheet Sheet1 (2) active, it will throw an error if the macro run where the sheet Output is active.

[halimgunawan]

Ok @karmapala, thanks so muchhh..

[karmapala]

You're welcome. Glad that you're helped .

BTW, the code is not clean.
There are some lines which are repeated for three times.

Let's hope our senior can give another code solution which is nicer, shorter, faster and better .

[halimgunawan]

@Karmapala, do you mean the lines repeated on output sheet?

[karmapala]

@Karmapala, do you mean the lines repeated on output sheet?

No... what I mean is that in the code, there a are some lines which are repeated a few times.

Tested only based on the data in your sample workbook.

After I made a random data variation with positive/negative number for the shops,
it turned out I made a mistake as the code before still not correct with the data variation I made.

Please try this one :

Please Login or Register  to view this content.

[halimgunawan]

@Karmapala,

pls see my new attached file...
I added stock field WH