VBA code procedure to generate production sheet.

Anbuselvam Kovilmani 41 Reputation points
2021-06-22T05:01:13.81+00:00

Dear Excel Genius

I have explained below my requirements for the Worksheet_Change procedure.

Target cells F3, C4 and E4 in the sheet "Batch Card", These target cells need to match column B, C and D in the sheet "Formulation" respectively.

From Formulation sheet J5 to CH47 data to extract as per the matches target cells value from Batch card. then it should generate the rows of data from cell A7.

Some product having 1 part and some have 4 part. So depends on the number of RM used in the matched product the row to be adjusted from row 7 in the sheet batch card.

All the products and their parts % is not the same which is mentioned in the formulation sheet column F to I.

Below is some examples batch card image link which is created with the older version.

2 Part Products. https://1drv.ms/u/s!AiSRNak3QYCf6iAXK2wX5XefTtnU?e=Rh6gD1

4 Part Products. https://1drv.ms/u/s!AiSRNak3QYCf6iE4YCnXWjkIS7dK?e=r0iOgd

The first image has two-part and the second image have four-part products. As per the parts and the RM, the rows need to insert or delete. Also, the row height must be adjusted (Part Headings 40 and the RM 30)

After all the parts the total of each product needs to calculate in the next to next row from the last used RM. Example in the second image cells E38 and F38.

As per the attached sheet batch card From row 45 to 49 in column D, data to be filled from the formulation sheet column CU: CV.

The attached sheet has only 43 products but in the actual is nearly 1000 products so the lookup range needs to be extended in the code.

Hope I have explained as much as I can. Need your valuable support to produce the output.

Thanks in advance.

The file link

https://1drv.ms/x/s!AiSRNak3QYCfgdcC0eO56sCMyxQi6Q?e=RNfIAv

Sincerely Yours
Anbuselvam K

0 comments