Description

Background:

There are some pages in the excel file.

“Sheet 1” is last week’s inventory data of each store (total 11 stores till now: 15001~15011), including “existing stock Qty”(till last Sunday) & total “Sales Qty”(last Monday till Sunday). I’ll get this file (updated data) every Monday.

“Upcoming delivery” is coming replenishments data. The Qty will be delivered to each store this week (around Wednesday) accordingly.

Every Monday,

~Firstly, I’ll input updated data into the “Sheet 1” & “Upcoming delivery” (copy from other sources).

~Secondly, go to “sheet 2”.

~Then, I’ll input how many goods (inventory per week) I want to replenish.

For example, if I want to replenish 2-week goods, I’ll input “2” in column “H1”. Then, press “Start”, the result will be calculated automatically for each store after the VBA run.

However, if I don’t want to replenish “2-week goods” to each store and want to replenish different Qty(inventory/week) to each store, then I’ll directly input preferred “inventory/week Qty” into column “R1~AB1” respectively.

Let say, input 4 into “R1” if I want to replenish 4-week goods to “store 15001” while input 3 into “S1” to replenish 3-week goods to “store 15002” and input 1 into “T1”, “U1”, “V1”, “W1” “X1”, “Y1”, “Z1”, “AA1” & “AB1” to replenish only 1-week goods to “store 15003 ~ 15011”.

~Then, as mentioned, press “Start”, the result (suggested order) will be provided automatically for my reference and final amendment.

*If there are more new stores added/inserted (let say 15012, 15013, 15014…) in the sheet 1, it will be also popped up in the sheet 2 after the VBA is run accordingly. (The programmer has already anticipated there will be more stores in future.)  

Problem & request:

1. After the VBA is run in the sheet 2, you can see “Classification Name”(column “C3”), there are 4 types: “Accessory”, “Frame”, “Package” & “Sunglasses”.
2. Is it possible to amend the VBA program this way?

~Now, it can be counted with “inventory/week’s replenishment” for all store or each store. That means, if I input “2”, everything will be counted (replenished) with “2-week” whatever “Accessory”, “Frame”, “Package” or “Sunglasses”.

~However, if I want to replenish “3” week’s “Frame” but only “1” week’s “Sunglasses to each store, it can’t be do so!

&

3. you can also see “Present selling price”(column “H3”), there are many types: “from 1 till 2,380”.

~Now, it can be counted with “inventory/week’s replenishment” for all store or each store. That means, if I input “2”, everything will be counted (replenished) with “2-week” whatever “1”, “280”, “480” or “980”...

~However, if I want to replenish more “780” & “980” (let say “4” week’s) but only “2” week’s for others to each store, it can’t be do so!

4. Therefore, is there any room to improve this VBA for more detailed calculation? At least let me also choose replenishment week for

“Frame” & “Sunglasses”

“Present selling price”

~But please keep all existing functions including possible new stores to be inserted in future easily (as mentioned).

~Or you can help design another better VBA?

~Will the VBA run too slowly if add the functions?

Thanks.

Instruction Files