Copying and Pasting a Value Once a Formula Has Run

[Longknife]

Hello,

I'm somewhat new to the Excel world and I keep pushing the limits of my skills trying to get it to do certain things for me.

What I want is to save, or log, a value once a formula has run. I need to keep track of how many bed spaces are occupied on a day to day basis and log/compile that information

I created a formula that returns a value, for that day, by counting the rows which have a name in them (meaning the room is occupied). The trouble is, once that day passes, that value is lost.

Is there some VBA code I can run to take this value and paste it somewhere? Ideally the code wouldn't run once that date has passed.

Below is the table I wanted to have the numbers logged into and this is the formula for the daily calculation (for cell c12): =IF(B12=TODAY(),COUNTIF('ROOMING LIST'!F6:F471,"<>"&""),"")

I was hoping I could do something without macros or VBA, but it's not looking that way! I really appreciate any help. Cheers.

111.jpg

[PCI]

A possibility could be to record a copy of the data when closing the file
A macro can be fired
See attached, for more detail send an Excel file
There is 2 macros
in a module

Please Login or Register  to view this content.

in ThisWorkbook module

Please Login or Register  to view this content.

[Longknife]

Sorry, I am quite the newbie. Does the first bit of code just go in a VBA sheet and the second in a module?

I've uploaded the xlsx. I can add another column beside each day for the value to be pasted upon each closing of the document.

Again, much appreciated.

I never would have thought that recording such basic data would have been such trouble.

[PCI]

See your file updated
There a tab named "STATISTICS_Save" to record data
Formulas have been changed for testing purpose and are doing reference to cell A1 instead of TODAY(): Change back for your need
See how formula are doing refrence to cell $L$2
There is a button "Save" to update the record.
The save treatment is launched when closing the file to remind to save data

[Longknife]

Merci!

Much appreciated. As I say, I'm quite new to all this and I'm a little surprised how complex a solution is required for a relatively simple issue. Even then, it's not fully automated in a sense.

Although I'm struggling to comprehend the VBA solution in various modules (I'm trying) I do at least appreciate the clever yet simple formula for having the value displayed reverse and draw from the saved table (once a date has passed).

Thanks again.

See your file updated
There a tab named "STATISTICS_Save" to record data
Formulas have been changed for testing purpose and are doing reference to cell A1 instead of TODAY(): Change back for your need
See how formula are doing refrence to cell $L$2
There is a button "Save" to update the record.
The save treatment is launched when closing the file to remind to save data