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.

 

[aschmidt2012]

Bump. If this isn't even possible I understand. Even a point in the right direction would be great.

 

[fredlo2008]

Hi,

This code will clear the data in cells A1:A3 everyday. You will close the workbook and next day the data will be cleared. is this what you are looking for?

Private Sub workbook_Open()

If Sheets("Aux").Range("A1").Value < Date Then
    Sheets("Data").Range("A1:A3").Value = vbNullString
    Sheets("Aux").Range("A1").Value = Date
End If


End Sub

 

[aschmidt2012]

Hi,

This code will clear the data in cells A1:A3 everyday. You will close the workbook and next day the data will be cleared. is this what you are looking for?

Private Sub workbook_Open()

If Sheets("Aux").Range("A1").Value < Date Then
    Sheets("Data").Range("A1:A3").Value = vbNullString
    Sheets("Aux").Range("A1").Value = Date
End If


End Sub

First of all, thank you for your reply! I believe this is definitely a step in the right direction. If I was to have all unlocked cell content deleted for all 4 worksheets every night (like you have shown above), how would I go about doing that? Would it just be ClearContents?

 

[aschmidt2012]

I'm not sure if this will help or not, but the groups of cells I want deleted every night are as follows:

Worksheet 1 (Named CSF1)
A5:C14, A18:C27, A31:C40, A44:C53, A57, C66

Worksheet 2 (Named CSF2)
A5:C14

Worksheet 3 (Named CSF3)
A5:C14, A18:C27, A31:C40, A44:C53, A57, C66, A70:C79

Worksheet 4 (Named CSF4)
A5:C14, A18:C27, A31:C40, A44:C53

 

[fredlo2008]

Try this. Sheet named "Aux" is an auxiliary sheet that can be hidden it will just let us keep track of the date.

This code will go through all the sheets in the file and clear Range("A1:A3")

Private Sub workbook_Open()

Dim i As Long
Dim icount As Long


icount = Sheets.Count


For i = 1 To icount
    If Not Sheets(i).Range("A2").Value = vbNullString Then
        If Sheets("Aux").Range("A1").Value < Date Then
            Sheets(i).Range("A1:A3").Value = ClearContents
        End If
    End If
Next i


Sheets("Aux").Range("A1").Value = Date


End Sub

 

[fredlo2008]

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

 

[aschmidt2012]

Thank you!!!

 

[fredlo2008]

I am glad I helped you

 

[aschmidt2012]

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'm glad you helped me too. haha

One final question and then I'm done: What does this line of code mean?


If Sheets("Aux").Range("A1").Value < Date The

I'm trying to figure out what the A1 is for. Also, when I enter those lines of code, that line I just pasted shows up red. Is that okay?