Auto Clear of cell range on the first of the month.

[hannahkoch2]

I need help with either a function or VBA code that can automatically clear a range of cells for me on the first of every month

 

[mse330]

Welcome to the board

You can use the below VBA code which will check the date when opening the file. Just change the sheet name & the range to suite your need & ensure to save the workbook in .xlsm or .xlsb format

Private Sub Workbook_Open()

If Day(Date) = 1 Then Sheets("Sheet1").Range("A1:B5").ClearContents

End Sub

The above code should be placed in "ThisWorkbook" in VBE which can be accessed by hitting Alt+F11

 

[Joe4]

Welcome to the Board!

Just be aware that if you don't open the file on the first of the month, it won't work.
So if the first is on the weekend, and you do not open it on that day, the next day you do open it, it will not clear out the data.

Also, if you open it on the first of the month, and it clear out the data, and then you add new data, if you re-open again on that same day (the first of the month), it will clear the new data you just entered!

An alternative may be to have a cell store the last date that you cleared the data. Then, have Workbook_Open code that checks that date, and if the current month is different then the month in the stored date, then it will clear your data, update the stored date, and save your file.

That will make sure it clears out the data the first time you open it each month, and not again that same month (regardless of which day you open it on, in case you miss day 1).

That code would look something like this (I am using Z1 as the cell to store my date):

Private Sub Workbook_Open()

    If Month(Date) <> Month(Sheets("Sheet1").Range("Z1")) Then
        Sheets("Sheet1").Range("A1:B5").ClearContents
        Sheets("Sheet1").Range("Z1") = Date
        ActiveWorkbook.Save
    End If

End Sub