Can cells clear automictically each Monday?

Occasional Contributor

Hi I have a spreadsheet I enter responses from clients each Thur-Sun. 

Each week I have to clear the contents before starting it each week.

Is there a way it will clear itself automatically?

Any help would be appreciated. 

Cheers, Porcha 



2 Replies


You could create VBA code that clears the cells when the workbook is opened on Monday:

  • Press Alt+F11 to activate the Visual Basic Editor.
  • Double-click on ThisWorkbook under Microsoft Excel Objects in the Project Explorer pane on the left hand side.
  • Copy the code listed below into the code window.
  • Change "Responses" to the name of the sheet with the client responses.
  • Switch back to Excel.
  • Save the workbook as a macro-enabled workbook (.xlsm).
  • Make sure that you allow macros when you open the workbook.
Private Sub Workbook_Open()
    If Weekday(Date) = vbMonday Then
        With Worksheets("Responses")
            .Range("E2:F" & .Rows.Count).ClearContents
        End With
    End If
End Sub

@Hans Vogelaar thank you, I will give this a try and let you know how I go really appreciate the help. Cheers, Porcha