Macro to group rows with certain subtotal

I have a list of data in excel (many rows) and would like to group the rows based on subtotal of (pack qty) = 12.
Every row has different pack qty, so the macro can simply get the combination of qty, as long as the total = 12.

But the combination must be grouped by Color, Region#, Store Number.
You can check the data example in attached excel file.

Could anyone help to write the macro ? Because I have thousands of rows in 1 excel data that need to be processed.

Thanks a lot

ok..i have a question - what if there are rows where the sum of the pack qty is not equal to 12?

Hi.. sorry to add the information..
there's no problem if the sum of the rows not equal to 12..
the target would be, as long a the sum of the rows less or equal to 12.. but cannot exceed 12.

thankss..

How do you want to group the data? Should a blank row be inserted between the groups?

Yes, a blank row be inserted into the group of the rows. That would be great.

Thanks.

Hi, anyone can help on this ?

Please please.. thanks..

Will try something out today.

Try this code and let me know. Put it in a new module of your workbook and run it.

Please Login or Register  to view this content.

Let me know if you face any issues.

Hi.. seems your macro can run well, but my excel file keep hang there without responding.
So I cannot get the final result of it.

I've tried to close all excel and only open that excel and run the macro, but the result is still the same.

How is it at your end ? Do you manage to run it well in the excel that I attached for the sample file ?

Please help me.

Thanks.

Hi.. sorry, I manage to find how to stop it. I just press ESC in my keyboard and then choose to End the process.

I got the result already. Let me test with my real data.

Will update to you the soonest.

Thanks a lot.

You do not need to use the ESC to stop the code. I have just tweaked it a little bit and now it works fine. Just test it at your end and let me know if you face any issues.

Please Login or Register  to view this content.

Hi, I tested ur new macro using some real data.

But i found there are lot of data not being calculated also.
I mean, for the pack qty > 12, then it's ok not being calculated. But how about those less than 12 ? why not move to Sheet2 also ?

I've attached another excel which I've tested using the macro.
Please take a look.

Thanks.

I ran the code several times but am not able to find the reason why they are not moving. Maybe someone else here can help us out.