I'll try to be as explicit as possible... If this help or if needed, I'm running windows 10 and the latest excel version from the office 365 suite.
My workbook consist of 3 sheets as follow:
Template
Group
Master
Template is what will be taken as a naked template to copy data from the sheets Group and Master into the template using a VBA macro.
It consist of 9 columns (A to I) and it needs to be of a precise format (in order to save it as a text file later down the road).
The template will already have the proper format but perhaps it is safe to force the format in the VBA macro?
Group consist of multiple columns where I will only require 2 for the macro.
Column A and E which are respectively: (Col A) a Group identifier for a list of different part numbers, and (Col E) the amount of part numbers per given group. (for the record, a part number can only be part of 1 group only)
There is a total of 327 groups going from cell A2:A328 and are identified as follow (1, 10, 20, 40, 50, 60, 100, 500, 1000, 1020.... up to 9999)
Some groups has no items in to, many groups has 30 to 100 items, a few more has 100 to 400 and perhaps 3 or 4 have 4000 to 6000 in them
Master is the big one, but then again I only need a few columns from it in order to create.
Col E is the part number, G is the group in which the item belong, H is the supplier, I is the supplier part number, T is Cost, then X,Y,Z,AA are all different sales price
I have a header for description and sorting in row 1 and 2 of this sheet, so all the actually data starts at row 3 and goes to about 30,000 lines
Those created sheets will eventually be saved as PRN file type (which explain the very specific format that is to be respected on my Template sheet), to then import data into another system.
My only problem is that the system has big issue when important a LARGE amount of lines and its most preferable to import a maximum of 1000 to 2000 at a time.
I need the script to create a sheet every time the SUM of the amount of parts per group (Col E2:E328) does not surpass 1500 (or preferably a manually determined amount that I could write in a cell of a 4th sheet to try different import size). The SUM check needs to be done in order of rows in sheet group and NOT try to find the best SUM match to reach 1500 or as close to it. So that if row 2 to 6 which consist of group 1, 10, 20, 40, 50 has a total SUM of 1610, it should ignore row 6 which is group 50 and then create a sheet for all the items of group 1, 10, 20, 40. So basically the group order is very important. It needs to follow the row order even group is given in Group!A2:A328.
Once the SUM check has identified the groups it will use for the sheet creation, it needs to go in the Master sheet and find all the part that belongs to the identified group. The lookup column in master sheet is G, which is the same as col E from the sheet group. Then it needs to copy VALUES (many cells in the Master sheet are formulas, so It need the VALUES to be copied) of all of these respective columns from Master to Template for every line matching the identified group during the SUM check: Col E from Master to "New Sheet" Col A, Col G from Master to "New Sheet" Col B, Col H from Master to "New Sheet" Col C, Col I from Master to "New Sheet" Col D, Col T from Master to "New Sheet" Col E, Col X from Master to "New Sheet" Col F, Col Y from Master to "New Sheet" Col G, Col Z from Master to "New Sheet" Col H, Col AA from Master to "New Sheet" Col I.
Note that my template has a header of 3 rows. I only need 1 row for the header on the generated sheets... I keep row 1 and 2 in the template sheet because I need the info written there as a reference and also because the info is related to the template, otherwise I would put it somewhere else. So before any data is to be copied, the script need to delete row 1 and 2 entirely. This will leave me with my header on row 1 and the copied values can start at row 2. The sheet created needs to be named dynamically based on the group it copies. In the example above where the SUM check identified that the group 1, 10, 20, 40 meet the amount of maximum part criteria, the name of the newly generated sheet should be "Import 1 to 40".
Once this is done, the script needs to rinse and repeat until all the groups are part of a newly generated sheet. Therefore, after having generated "Import 1 to 40", the next sheets would logically start with "Import 50 to ????", "Import ???? to ????"... all the way up to "Import ???? to 9999" where 9999 is the last possible group. Note that very few groups do have more then 1500 items in them. In this instance, the script should not return error and generate a sheet for the entire group. As much as I hate having that many items in 1 import, I cannot split the groups. Also if all the sheets are already created, ideally I would prefer if the script does not return an error if ran again.
I've spent hours and tried the best I can to automate things in excel... but it has its limit. It seems VBA is my only solution for what I need and I've hit a wall. I must admit, VBA is not my cup of tee as the most of you should have probably figured at this point if you dared long enough to read this far. I could really use a hand on this issue.
Best regards,
Vincent
My workbook consist of 3 sheets as follow:
Template
Group
Master
Template is what will be taken as a naked template to copy data from the sheets Group and Master into the template using a VBA macro.
It consist of 9 columns (A to I) and it needs to be of a precise format (in order to save it as a text file later down the road).
The template will already have the proper format but perhaps it is safe to force the format in the VBA macro?
Group consist of multiple columns where I will only require 2 for the macro.
Column A and E which are respectively: (Col A) a Group identifier for a list of different part numbers, and (Col E) the amount of part numbers per given group. (for the record, a part number can only be part of 1 group only)
There is a total of 327 groups going from cell A2:A328 and are identified as follow (1, 10, 20, 40, 50, 60, 100, 500, 1000, 1020.... up to 9999)
Some groups has no items in to, many groups has 30 to 100 items, a few more has 100 to 400 and perhaps 3 or 4 have 4000 to 6000 in them
Master is the big one, but then again I only need a few columns from it in order to create.
Col E is the part number, G is the group in which the item belong, H is the supplier, I is the supplier part number, T is Cost, then X,Y,Z,AA are all different sales price
I have a header for description and sorting in row 1 and 2 of this sheet, so all the actually data starts at row 3 and goes to about 30,000 lines
Those created sheets will eventually be saved as PRN file type (which explain the very specific format that is to be respected on my Template sheet), to then import data into another system.
My only problem is that the system has big issue when important a LARGE amount of lines and its most preferable to import a maximum of 1000 to 2000 at a time.
I need the script to create a sheet every time the SUM of the amount of parts per group (Col E2:E328) does not surpass 1500 (or preferably a manually determined amount that I could write in a cell of a 4th sheet to try different import size). The SUM check needs to be done in order of rows in sheet group and NOT try to find the best SUM match to reach 1500 or as close to it. So that if row 2 to 6 which consist of group 1, 10, 20, 40, 50 has a total SUM of 1610, it should ignore row 6 which is group 50 and then create a sheet for all the items of group 1, 10, 20, 40. So basically the group order is very important. It needs to follow the row order even group is given in Group!A2:A328.
Once the SUM check has identified the groups it will use for the sheet creation, it needs to go in the Master sheet and find all the part that belongs to the identified group. The lookup column in master sheet is G, which is the same as col E from the sheet group. Then it needs to copy VALUES (many cells in the Master sheet are formulas, so It need the VALUES to be copied) of all of these respective columns from Master to Template for every line matching the identified group during the SUM check: Col E from Master to "New Sheet" Col A, Col G from Master to "New Sheet" Col B, Col H from Master to "New Sheet" Col C, Col I from Master to "New Sheet" Col D, Col T from Master to "New Sheet" Col E, Col X from Master to "New Sheet" Col F, Col Y from Master to "New Sheet" Col G, Col Z from Master to "New Sheet" Col H, Col AA from Master to "New Sheet" Col I.
Note that my template has a header of 3 rows. I only need 1 row for the header on the generated sheets... I keep row 1 and 2 in the template sheet because I need the info written there as a reference and also because the info is related to the template, otherwise I would put it somewhere else. So before any data is to be copied, the script need to delete row 1 and 2 entirely. This will leave me with my header on row 1 and the copied values can start at row 2. The sheet created needs to be named dynamically based on the group it copies. In the example above where the SUM check identified that the group 1, 10, 20, 40 meet the amount of maximum part criteria, the name of the newly generated sheet should be "Import 1 to 40".
Once this is done, the script needs to rinse and repeat until all the groups are part of a newly generated sheet. Therefore, after having generated "Import 1 to 40", the next sheets would logically start with "Import 50 to ????", "Import ???? to ????"... all the way up to "Import ???? to 9999" where 9999 is the last possible group. Note that very few groups do have more then 1500 items in them. In this instance, the script should not return error and generate a sheet for the entire group. As much as I hate having that many items in 1 import, I cannot split the groups. Also if all the sheets are already created, ideally I would prefer if the script does not return an error if ran again.
I've spent hours and tried the best I can to automate things in excel... but it has its limit. It seems VBA is my only solution for what I need and I've hit a wall. I must admit, VBA is not my cup of tee as the most of you should have probably figured at this point if you dared long enough to read this far. I could really use a hand on this issue.
Best regards,
Vincent
