VBA to reset my sheet when the file is opened for the first time each day.

[danbates77]

Hi,

Please could someone help me?

Does anyone know a way of resetting a sheet via VBA when the file is opened for the very first time each morning and only the very first time because the file will be opened multiple times per day but I only need it to reset on the very first occasion.

Any help would be very appreciated.

Thanks

Dan

 

[My Aswer Is This]

Hi,

Please could someone help me?

Does anyone know a way of resetting a sheet via VBA when the file is opened for the very first time each morning and only the very first time because the file will be opened multiple times per day but I only need it to reset on the very first occasion.

Any help would be very appreciated.

Thanks

Dan

Define: "resetting a sheet"

I'm not familiar with that term.

 

[danbates77]

Hi,

So let's say I have random numbers in cells A1 down to A10 with various numbers in them. I would like all the cells from A1 to A10 to be reset back to zero when the file is opened for the very first time in the morning after 6am but I don't want the cells to be reset when the file is opened for the second time or third time and so on.

I hope this helps a little more.

 

[noveske]

Hi,

So let's say I have random numbers in cells A1 down to A10 with various numbers in them. I would like all the cells from A1 to A10 to be reset back to zero when the file is opened for the very first time in the morning after 6am but I don't want the cells to be reset when the file is opened for the second time or third time and so on.

I hope this helps a little more.

So let's say you just have a cell somewhere with the date.
This will check the date. If current date is later than the date saved, it will clear contents of cells.
Just set the range.
After it's cleared it will input the current date to the chosen cell.

When current date = date in cell, then it won't run.

I placed this in ThisWorkbook.

Replace B1 with an empty cell somewhere. Enter today's date if you already opened it the first time today.

Tested and working.

Private Sub Workbook_Open()

    Application.EnableEvents = False

    If Date > Range("B1").Value Then
        Worksheets("Sheet1").Range("A1:10").ClearContents
        Range("B1").Value = Date
    End If
 
        Application.EnableEvents = True

End Sub

 

[danbates77]

Hi noveske,

Thank you very much for your help. It works perfectly.

Thanks

Dan