VBA Code for date lookup and data transfer (god please help)

hoofy

New Member
Joined
Nov 23, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I need to make a stock tracker in excel. It will be a shared document and there is going to be some not very excel proficient individuals using it. Essentially, I need to have a table which they fill out two columns: the stock (consumables) used [column a] or the stock level that day [column b], or both. If they fill out neither, then the cells are left blank.

Then, they need to press a button that executes the following process:
1. Look at today's date
2. Copy the information from consumable#1 [column a] into a master list where the daily consumption and stock level is recorded
2.1 Repeat for all 4 consumables (they each have a separate master list)
3. Copy the information from consumable#1 [column b] into the same master list
3.1 Repeat for all 4 consumables

The issue I keep running into is I only want it to update that specific day, and keep all previous records untouched. For clarification, this master list then forecasts when we will run out of stock (the daily consumption is averaged) and based on delivery times for each consumable it provides the last day we could order to get new stock in time for running out.

Any help would honestly be tremendous. I have no idea how to use VBA and god would this end an ongoing nightmare for me.
See attached screenshots for clarification.
 

Attachments

  • Table 2.png
    Table 2.png
    9.5 KB · Views: 18
  • Table 1.png
    Table 1.png
    3.9 KB · Views: 17

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Forum statistics

Threads
1,223,270
Messages
6,171,103
Members
452,379
Latest member
IainTru

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top