Can cells clear automictically each Monday?

%3CLINGO-SUB%20id%3D%22lingo-sub-2731377%22%20slang%3D%22en-US%22%3ECan%20cells%20clear%20automictically%20each%20Monday%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2731377%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20I%20have%20a%20spreadsheet%20I%20enter%20responses%20from%20clients%20each%20Thur-Sun.%26nbsp%3B%3C%2FP%3E%3CP%3EEach%20week%20I%20have%20to%20clear%20the%20contents%20before%20starting%20it%20each%20week.%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20it%20will%20clear%20itself%20automatically%3F%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20appreciated.%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%2C%20Porcha%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Porcha_0-1631080923537.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F308809i5FD9B848F9981823%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Porcha_0-1631080923537.png%22%20alt%3D%22Porcha_0-1631080923537.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2731377%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2731676%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20cells%20clear%20automictically%20each%20Monday%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2731676%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1147519%22%20target%3D%22_blank%22%3E%40Porcha%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20could%20create%20VBA%20code%20that%20clears%20the%20cells%20when%20the%20workbook%20is%20opened%20on%20Monday%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EPress%20Alt%2BF11%20to%20activate%20the%20Visual%20Basic%20Editor.%3C%2FLI%3E%0A%3CLI%3EDouble-click%20on%20ThisWorkbook%20under%20Microsoft%20Excel%20Objects%20in%20the%20Project%20Explorer%20pane%20on%20the%20left%20hand%20side.%3C%2FLI%3E%0A%3CLI%3ECopy%20the%20code%20listed%20below%20into%20the%20code%20window.%3C%2FLI%3E%0A%3CLI%3EChange%20%22Responses%22%20to%20the%20name%20of%20the%20sheet%20with%20the%20client%20responses.%3C%2FLI%3E%0A%3CLI%3ESwitch%20back%20to%20Excel.%3C%2FLI%3E%0A%3CLI%3ESave%20the%20workbook%20as%20a%20macro-enabled%20workbook%20(.xlsm).%3C%2FLI%3E%0A%3CLI%3EMake%20sure%20that%20you%20allow%20macros%20when%20you%20open%20the%20workbook.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3EPrivate%20Sub%20Workbook_Open()%0A%20%20%20%20If%20Weekday(Date)%20%3D%20vbMonday%20Then%0A%20%20%20%20%20%20%20%20With%20Worksheets(%22Responses%22)%0A%20%20%20%20%20%20%20%20%20%20%20%20.Range(%22E2%3AF%22%20%26amp%3B%20.Rows.Count).ClearContents%0A%20%20%20%20%20%20%20%20End%20With%0A%20%20%20%20End%20If%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2747549%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20cells%20clear%20automictically%20each%20Monday%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2747549%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3Bthank%20you%2C%20I%20will%20give%20this%20a%20try%20and%20let%20you%20know%20how%20I%20go%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%20really%20appreciate%20the%20help.%20Cheers%2C%20Porcha%3C%2FP%3E%3C%2FLINGO-BODY%3E
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 

Porcha_0-1631080923537.png

 

2 Replies

@Porcha 

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