Automatically delete columns after date has passed?

Hi, I have a simplified sample worksheet with columns of weekly dates (columns C through V) and several employees listed in the rows. The cells contain simple data about what day the employee is working that week or if not working at all (eg "MON", "TUE" or "X").

I would like the worksheet to automatically update so that once a week has passed, that column is deleted and a new week is added to the end. So in my sample, the weeks start at 3/18/2019 in col C. When the date becomes 3/25 (col D), I would like the data under column C to be deleted and for everything to move to the left and a new week to be added on so that I still have columns C-V. I probably haven't explained this very well, but I'm sure you'll understand what I mean by looking at the worksheet. I presume I need a macro to do this, but if I could accomplish it another way that would be good too. The main thing is that I'd like this to happen automatically when I open the worksheet if possible.

I have looked this up online, but haven't been able to understand the examples I looked at. Thanks for any assistance.

Paste this in your 'ThisWorkbook' module and it will fire every time you open the file.
If the date in C1 is older than a week it will delete that column and add a new one to the end.

Please Login or Register  to view this content.

Note it does not reproduce the colour banding you have to separate weeks (it's merely meant to point you in the right direction) and if you don't open the file for more than a week (i.e. there is more than one column to delete) it'll only delete the first column.

It can be adapted to deal with all that with some effort.

BSB

Thank you so much