Forum Discussion
Autocopy data from a cell to another cell based on current date
Hi
Ok - I understand the problem now.
Using TODAY() will never work as you already spotted when you move to the next day the previous days data is overwritten.
The question I have for you is how important is for you to have an automated process to achieve this? It seems to me simpler for you to copy/paste the value in the appropriate cell in Table 2?
What you want can be achieved via some VBA coding - but I think it is using a sledgehammer cracking a nut!
I attach an example which includes some very crude code to copy cell J17 to the cell for Feb 18 (p27).
If you clear the date in cell P27 and go to another sheet (sheet3) and then select sheet2 cell P27 will be populated with J17 data.
At the moment this only works for cell P27. The code would need to be amended to find the appropriate cell each day (based on the current date) in order to paste the data.
I think this is too complicated to achieve - a simple copy / paste each day is simpler.
sorry!
Peter
In the example you have the date as Feb 10. Where on sheet 2 do you want to copy the value of J17?
Can you share your file?
thanks
Peter
Hello, peteryac60!
Thank you for checking my query.
Please see the attached file. This is more or less how the data will looks like, but for confidentiality, I replaced the content, so please do not mind if there are miscalculations as I just input whatever came to mind.
Anyway, there are 2 tables on the same sheet.
In Table 1, the percentage data (10.59%) on cell 'J17' is reflected on the cell 'P27' on Table 2.The condition is if the date is Today (cell 'B3'), the corresponding date on Table 2 should automatically update/ copy the value on J17. The variable is the value on J17 coz that changes everyday.
Simply put, since today is February 18, 2021, cell 'P27' (which is Feb. 18 as well) copied the 10.59% from J17. On Feb. 19, 'P27' data must not change, but P28 should copy the new value on J17 instead.
My current formula is =IF($B$3=TODAY(),$J$17,0), but by using this, if the day changes to Feb. 19, 2021, cell 'P27' will be the same with cell 'P28.' I want it to stay as 10.59%, so I could see how the "Portfolio Gain" is progressing on a daily basis.
I hope my explanation didn't confuse you, and I also hope you could help me come up with a formula to automate this data. Looking forward to your reply.
- GervieHi
Ok - I understand the problem now.
Using TODAY() will never work as you already spotted when you move to the next day the previous days data is overwritten.
The question I have for you is how important is for you to have an automated process to achieve this? It seems to me simpler for you to copy/paste the value in the appropriate cell in Table 2?
What you want can be achieved via some VBA coding - but I think it is using a sledgehammer cracking a nut!
I attach an example which includes some very crude code to copy cell J17 to the cell for Feb 18 (p27).
If you clear the date in cell P27 and go to another sheet (sheet3) and then select sheet2 cell P27 will be populated with J17 data.
At the moment this only works for cell P27. The code would need to be amended to find the appropriate cell each day (based on the current date) in order to paste the data.
I think this is too complicated to achieve - a simple copy / paste each day is simpler.
sorry!
Peter
Hi again, peteryac60!
I understand.
For now, since this is for personal use, I could go on without the automation, but I might encounter the same for work purposes in the future. Actually, copy-pasting the previous data on a daily basis is exactly what I am doing in the sheets. I've been thinking all this time that there might be a simple formula that I could use, but at least now, I know that this is more complicated than I thought, and it would require me a VBA coding. I tried your instruction to clear P27, go to sheet 3, and rechecked P27. Indeed, the cell was auto-populated, and I'm amazed. 🙂
I guess I'll stick with manual copy-paste while I'm trying to learn more on VBA, and gain more skills like yours. Really appreciate your time and effort to respond. Thanks a lot, Peter! Cheers, and stay safe. 🙂
