VBA code-Assistance Sought

[Latlonchy]

I am not an expert in excel but am just passionate about excel. I am very eager to explore and learn more.

Here is my question. Can someone assist with a VBA code or any other effective way of getting the task done.
TASK
I want to create an automated single row inventory tracker with the following columns:
Column A: Date, Column B: Inventory items in a drop down list, Column C: Opening inventory balance, Column D: Units purchased, Column E: Units sold, Column F: Closing inventory balance.
I want a formula or code which automatically retrieves the closing balance and captures it as an opening balance for the next day. I want this to always happen when the date changes. The formula should always work for every inventory item selected from the drop down list. Please note, I don't have problems with setting automatic date formula. When the opening balance has been captured, I want excel to visit some sheets on the same workbook and delete some specified columns. I also don't have problems with setting up the drop down list. May you please help. Thank you

 

[rpaulson]

I would probably keep all the data in a Table on a sheet.
A - Date
B - Item Drop Down
C - Units Purchased
D - Units sold

then create a Pivot Table on a separate sheet.
Pivot table can have calculated fields to keep running inventory, starting inventory and daily closing inventory.

 

[Latlonchy]

I would probably keep all the data in a Table on a sheet.
A - Date
B - Item Drop Down
C - Units Purchased
D - Units sold

then create a Pivot Table on a separate sheet.
Pivot table can have calculated fields to keep running inventory, starting inventory and daily closing inventory.

Thanks for assisting, your advice is working pretty well.