Delete cell content every day?

[aschmidt2012]

Hello everyone! I was wondering if anyone knew of a macro that would be able to delete a specified series of cell contents (different cell groups--not just a singular group of cells) at the start of every day at midnight. If anyone could help or at least point me in the right direction it would be greatly appreciated! Thank you in advance for your time and assistance.

 

[fredlo2008]

Well this line will work as long as there is a sheet in a workbook named "Aux" This sheet can be hidden so others cannot see it. It will help us keep track of date. So the meaning of the line of code says. If the value in "Aux" in A1 is less than today's date then do something...the clearing of the data.

BTW the code I gave you has to be placed under ThisWorkbook Module. This is a event. It will be triggered everytime someone opens the workbook the date after the day it was closed. If you have it open at midnight you will have to close it to trigger the code.

 

[sdkorin]

This is the modified code to fit your needs.

Private Sub workbook_Open()

If Sheets("Aux").Range("A1").Value < Date The
    Sheets("CSF1").Range("A5:C14, A18:C27, A31:C40, A44:C53, A57, C66") = ClearContents
    Sheets("CSF2").Range("A5:C14") = ClearContents
    Sheets("CSF3").Range("A5:C14, A18:C27, A31:C40, A44:C53, A57, C66, A70:C79") = ClearContents
    Sheets("CSF4").Range("A5:C14, A18:C27, A31:C40, A44:C53") = ClearContents    
End If
Sheets("Aux").Range("A1").Value = Date
End Sub

I am trying this and am getting a "compile error: syntax error". I am admittedly not big on VB and am a bit lost. Here is what I'm trying.

Private Sub workbook_Open()
If Sheets("PM Southeast Rotation").Range("AB2").Value < Date
Sheets("Southeast Screening Data").Range("C9:P12,C23,C25,F23:F25,L16:L19,L21:L25,M18,M20,M22,M25,M27,M29,O18:R30,E30:K30,T9:U19,S21,C36:R39,C50,C53,F50:F52,E58:K58,M45,M47,M49,M52,M54,M56,O45:R58,U36:U46,S48,L43:L46,L48:L52") = ClearContents
Sheets("AM Southeast Rotation").Range("A5:D22,E5:E22,F5:F22,H5:I22,K5:M22,O5:T22,V5:X22,Z5:AB22,AD5:AI22,T2,W2,AG2,H36:Y42") = ClearContents
Sheets("PM Southeast Rotation").Range("A5:D22,E5:E22,F5:F22,H5:I22,K5:M22,O5:U22,W5:Y22,AA5:AC22,AE5:AI22,U2,X2,AH2,H36:AA42") = ClearContents
End If
Sheets("PM Southeast Rotation").Range("AB2").Value = Date
End Sub

Thoughts anyone?

 

[My Aswer Is This]

It should look something like this:

Sheets("PM Southeast Rotation").Range("A5:D22,E5:E22,F5:F22,H5:I22,K5:M2").[COLOR=#ff0000]ClearContents[/COLOR]

 

[fredlo2008]

It should look something like this:

Sheets("PM Southeast Rotation").Range("A5:D22,E5:E22,F5:F22,H5:I22,K5:M2").[COLOR=#ff0000]ClearContents[/COLOR]

Today, 6 years after I wrote this post; all I have to wonder... "what the heck was I thinking" lol Good catch!

 

[airingersoll]

So I tried to follow this thread, but I feel like im missing something. Right now im trying to build a tracker that shows my employees/coworkers leave days and I want it to auto delete the previous days content (cell range "D3:D34). I created a sheet called AUX with the current date added in A1 (I used macro =TODAY()). So if I wanted to delete those specified cells daily would I use the following code:

Private Sub workbook_Open()
If Sheets("AUX").Range("A1").Value < Date Then
Sheets("LEAVE TRACKER").Range("D3:D34").ClearContents
End If

End Sub