kandycoder / inventory Goto Github PK

The quantity column needs to be aggregated on a weekly basis.

In the destination spreadsheet, a block of columns serves to store the quantity delta for each item for a given week. When the macro imports changes to the quantity column, it should therefore also add those quantity adjustments in the appropriate column for that week (with a new column being created if needed).

Background
An existing macro already adds new item quantities from one spreadsheet to their respective in-stock quantity in another sheet to update an inventory. It scans the source sheet for new rows, and for every new row it gets the item id and quantity values from that row. The macro then looks up the item id in the destination sheet and adds the quantity to the corresponding quantity field of the destination sheet. (The destination sheet contains total items in stock.)

Task Description
The job is to implement the following: if the macro is run and a new week has begun, a new column is to be created in the destination sheet with a heading for that week (such as "2023-01-15"). The new column should be created before the existing logic runs. Then, logic needs to be added to add/accumulate item quantities in the weekly column similarly to how it is already being done for the total quantity in stock. When the macro is run again in the same week, no new column is created but the quantities in the column for that week are updated again. The end result is that in addition to having the total quantity in stock, there will be new columns for every week containing the quantity delta for that week.

The aggregate data needs to be split into two columns per week, with adds in one column and subtracts in the other.

Need to use Sheets(...).Columns(...).Column to convert column letter(s) to index.