You will have to do it using code because formulas are dynamic and will always change to reflect current data.
See if this will get you started.
This code is intended to go into the Workbook Module name "ThisWorkbook" and it used the Workbook_Open event.
The current date is tested to see if it is the first day of the month and, if so, a message box is displayed prompting the use to transfer the values now.
You can test the code by changing the 1 on this line to the current day, save & close the workbook, then re-open it.
ALWAYS test code on a back up copy of the workbook
Code for the Worksheet Module
You can use this code to manually transfer the values. If you don't open the workbook on the first day, the above code will not execute until the next month (assuming the file is opened on the 1st day).
This code goes into a standard module.
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks