Hi ALL,
I need some help to create a macro to compare quantities of 2 worksheets and then create a new worksheet showing quantity change. See attached sample excel file and work step when I done this exercise manually. The macro will help reduce time spent and possible error in manual manipulation.
I have only limited knowledge in creating VBA/macro. I appreciate if someone can help me on this Application.
MY WORK FLOW BELOW:
Workbook names information:
• Previous Design QTYs (worksheet name - Initial_MTO)
• New Design QTYs (worksheet name – 2nd_MTO), this will only contain values on column F(Current Design QTY)
• Output Current QTYs (worksheet name – Top-Up 1_MTO)
• Columns I(Current Order) & K(Delta) have formula
• Colum E(PartNo) is the Primary key
STEP 1:
• Copy Initial_MTO worksheet and name it as Top-Up 1_MTO. Note all item number will stay the same and no sorting for traceability for each Top-Up stages.
• Copy values on column I(Current Order) and paste value on column G(Previous Order)
• Delete all values on column F(Current Design QTY)
• Retain values on column H(Contingency) & column J(Surplus)
• Leave the calculated values on column K(Delta)
STEP 2:
• Copy all data from 2nd_MTO worksheet and append to the end entry of Top-Up 1_MTO worksheet
STEP 3:
• Do advance combined row using column E(PartNo) as the “PRIMARY KEY”, columns F(Current Design QTY) & column G(Previous Order) as “CALCULATE(SUM)”
• Highlight new items that are not included on worksheet Initial_MTO)
• Adjust numbering on column A(Item No.) for new added items.
• Current Order = Current Design QTY + Contingency – Surplus (QTY)
• Delta = Current Order – Previous Order
• Columns H(Contingency) & I(Surplus QTY) will be blank, input manually at a later stage.
I hope my manual manipulation work step is clear. Thank you in advance.
I need some help to create a macro to compare quantities of 2 worksheets and then create a new worksheet showing quantity change. See attached sample excel file and work step when I done this exercise manually. The macro will help reduce time spent and possible error in manual manipulation.
I have only limited knowledge in creating VBA/macro. I appreciate if someone can help me on this Application.
MY WORK FLOW BELOW:
Workbook names information:
• Previous Design QTYs (worksheet name - Initial_MTO)
• New Design QTYs (worksheet name – 2nd_MTO), this will only contain values on column F(Current Design QTY)
• Output Current QTYs (worksheet name – Top-Up 1_MTO)
• Columns I(Current Order) & K(Delta) have formula
• Colum E(PartNo) is the Primary key
STEP 1:
• Copy Initial_MTO worksheet and name it as Top-Up 1_MTO. Note all item number will stay the same and no sorting for traceability for each Top-Up stages.
• Copy values on column I(Current Order) and paste value on column G(Previous Order)
• Delete all values on column F(Current Design QTY)
• Retain values on column H(Contingency) & column J(Surplus)
• Leave the calculated values on column K(Delta)
STEP 2:
• Copy all data from 2nd_MTO worksheet and append to the end entry of Top-Up 1_MTO worksheet
STEP 3:
• Do advance combined row using column E(PartNo) as the “PRIMARY KEY”, columns F(Current Design QTY) & column G(Previous Order) as “CALCULATE(SUM)”
• Highlight new items that are not included on worksheet Initial_MTO)
• Adjust numbering on column A(Item No.) for new added items.
• Current Order = Current Design QTY + Contingency – Surplus (QTY)
• Delta = Current Order – Previous Order
• Columns H(Contingency) & I(Surplus QTY) will be blank, input manually at a later stage.
I hope my manual manipulation work step is clear. Thank you in advance.