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
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. 🙂
Hi
In order to access VBA you will need to add the developer tab to your ribbon.
Select anywhere on the ribbon and right click. You should see 'customize the ribbon' option. Select this and a window will pop up. Under the main tab section (a little more than halfway) is a 'developer' option. Check the box. Then hit OK. The developer tab will appear on the ribbon. This is just the fist step to VBA!
Get yourself a simple VBA book or look at YouTube - thee are some good tutorials there.
BTW - I am not sure if you noticed but the file I sent you had a "xlms" suffix as opposed to "xlsx". VBA will only work on "xlms" files!
If you are happy with the 'solution' I provided can you please mark this as complete.
many thanks and good luck!
Peter