Clearing Cell Contents Daily Automatically

Copper Contributor

Hello!

 

I am hoping someone can help me. I would like to automate a break sign up sheet to clear the contents of specific cells each day. If I set it up to clear after a specific period of time passes, then the cells won't all clear out, as people fill in the cells at different times to claim that slot. If I set it to a specific time, if that workbook is not opened at that exact time, it wont clear and if I do a range of hoirs, it might clear multiple times, each time it is opened (I think).

 

Is there any way for it to lool at the date, realize the cell contents are from the day before and clear those cells? 

 

Thank you!

2 Replies

@Gm12367 It appears that you have done some experimenting, and have tried code in the Workbook_Open event.  That seems good.  All your workbook needs is knowledge of when (which day) it last performed a clearing action.  That knowledge could be stored in a reserved cell in a reserved worksheet (which is possibly hidden).

Store an initial date value in your special cell.  Then, your Workbook_Open code should include something like:

    If Date > Sheets("SpecialData").Range("B2").Value Then
        '   It's time to do a cleaning.
        '[do the cleaning (either with code here or in a called procedure)]
        '   Record the date of latest cleaning:
        Sheets("SpecialData").Range("B2").Value = Date
    End If

 

Thank you, I will attempt this!