Forum Discussion
Updating a sheet by copying text from a cell to another cell after a day, for archiving purposes
I have two sheets in a file. Sheet 1 containts the daily meeting items. However, this is only the case for the last 5 workdays.
Monday is B2.
Tuesday is B5.
Wednesday is B8, et cetera
Every morning, the cell is overwritten for that day of the week.
How can you copy the cell (and the cell above containing the date) to sheet 2 after that day has ended?
In other words, copy B1 and B2 to sheet 2 after Monday. Copy B4 and B5 to sheet 2 after Tuesday et cetera.
I tried using WEEKDAY, but without succes.
Additional conditions:
- The Excel document must work with Microsoft Teams. To my knowledge, there isn't any way to have VBA/Macros working within Teams.
- The Excel document doesn't need to update 24/7. It is suffucient if it copies only when someone opens the document daily.
- The latest copied cell would preferably be in top row, thus having a colomn from new to old in sheet 2. (If not possible, colomn from old to new is also sufficient.)
2 Replies
This method is exemplary when VBA/macros are not an option, and since external automation isn’t possible with Teams, a manual approach will suffice. You could also add a note at the bottom of Sheet 2 to remind users to update the sheet daily, ensuring everyone stays on the same page.
Using Microsoft Power Automate to automate the process of copying data from one sheet to another in certain schedules or triggers. Another approach can be to use a personal computer solution with VBA to gather the data, but you would then need to manage the resulting Excel file.
