Generate sheets from template by rows and copying values to the template

Hi all,

I want to generate sheets from the template in the attachment. There's an example of how I want it to be generate.

There are some rules like the amount of the sheets generate for each row is based on column P.

Column J will be copy to the carton number in each sheets it will be increment say J2 = A then when it copy to the first sheet it will be A1 then A2 , A3 and so on until the last sheet of each row.

Then column M and N have a lot of combinations.

Column M is the 'Size' column normal it will have something like this in the cell '4-16' this is how we record the sizes and if there's a '-' in between the numbers it will always be an increment of 2 until the last number so for this it will be 4,6,8,10,12,14,16 and this information will be apply on the size part of the template.

But sometime it will apply like this '4+8+10+12' if there's a size missing which in here is 6 then we always record it like this.

There are size like S-XL and S+L which is the same concept so for 'S-XL it will mean S,M,L,XL and S+L means S,L and this is how I want the size column and the rules to be generate to the templates.

For the 'breakdown' column N is quite similar to column M, here's an example of what values are like in column M '23+45+(76+23)+12'

Each '+' sign indicate a different size so let say 23 is size 6, 8,10,12
the brackets indicate that they are the same size. so 76 and 23 inside the bracket are both size 10
How the size apply will be depends on size column like it could be '4+12+14+16' in size column and column N can still be '23+45+(76+23)+12'

Then for the other columns for colour, PO and SKU it will need to be copy accordingly so let say if this row need to generate 4 templates it will need to be copy to the 4 templates as shown on my example in the attachment.

Can this be done in vba?

Thanks

I'm sorry as I don't get exactly what you mean.

For example :

There are size like S-XL and S+L which is the same concept so for 'S-XL it will mean S,M,L,XL and S+L means S,L and
this is how I want the size column and the rules to be generate to the templates.

But in your sample workbook on each example sheet, the size is still a number, not a letter S or M or L, etc.

I also see that column H and column P has the same value on each row.
I wonder what is the possibility that "one day" the value on each row of column H is different than the value on each row of column P ?

What I assume is (for example)
Box : B
Column size row value : 16-26
while the value in Column QTY (same row) are : 17+31+(32+18)+31+25+6

So if it's side by side, it becomes like this :
BOX SIZE QTY
B1....16......17
B2....18......31
B3....20......32
B4....20......18
B5....22......31
B6....24......25
B7....26......6

Another example :
Box M
Column size, row value : 4+6+8+16
Column QTY value : 16+(12+53)+57+16

So if it's side by side, it becomes like this :
BOX SIZE QTY
M1....4.......16
M2....6.......12
M3....6.......53
M4....8.......57
M5....16.....16

That's if I'm not mistaken what you mean.

The code assumes that there will be only two quantities in the bracket.
Never : (12+53+17) ---> more than two qty.
Never : (10) ---> only one qty
but always : (46+23) ---> two qty.

Also, since you don't mention how you want to name the generated sheet,
I give the sheet name based on the Carton. No.

Anyway, please have a look at the attachment.

@karmapala

This solved the problem!

But I found out there are some other problems when I tested it with other data.

I just realised if there are more than 3 numbers inbetween the brackets there're errors. For exmaple like this (12+23+89+100+89).

Sorry I forgot to mention that there's this weird size call ONESIZE if there's such size apply in the size column it will all be call ONESIZE for all the lable generated.

Updated - I uploaded a data set
Thanks

Originally Posted by HXIO

@karmapala
I just realised if there are more than 3 numbers inbetween the brackets there're errors.

Yes of course it will throw an error .

I've put a note in my post before :

Originally Posted by karmapala

The code assumes that there will be only two quantities in the bracket.
Never : (12+53+17) ---> more than two qty.
Never : (10) ---> only one qty
but always : (46+23) ---> two qty.

there's this weird size call ONESIZE if there's such size apply in the size column
it will all be call ONESIZE for all the lable generated.

Ok.. I will try it.
My knowledge is still basic .

I hope now it works with more than two qty in the bracket.