running a macro annually/ continuously

[MRA]

Hey guys

I have a short macro that I want to run once per year on a specific date.
Specifically, I want to update the year column at the beginning of of each new fiscal year, which for me happens to be today July 1st.

Here's the macro

Sub Dates()
Worksheets("Sheet1").Range("D1:D1").Delete Shift:=xlShiftToLeft
End Sub

Just looking around here I've seen use of the wait command and such, but I'm looking for an arbitrary date reference if possible, rather than one that references the current time.

As a second question, I'd like this macro to run continuously, such that each time the workbook is opened the macro runs a check to see if the right date has been reached.
Also, I'd like a macro to automatically update cells based on data entered by the user, I was thinking I could adapt the answer to the previous question to fit this as well.

Thanks a lot guys you've been great so far
Mike

 

[MRA]

I've been messing with it some more and I came up with this

Sub Dates()
d = 2008 
If Date = DateSerial(d, 7, 1) Then _
Worksheets("Sheet1").Range("D1:D1").Delete Shift:=xlShiftToLeft
d = d + 1
Loop
End Sub

With this, all I need is a way to run the program once and have it check continuously. In other words, the way this is set up, running the macro once will check the date, and then increase it by one if there's a match. However, once the program is run again, the variable will reset to 2008 and thus the program will work today and never again .

PS
if you're a inexperienced like me, don't move the d=d+1 line outside of the loop... just trust me

 

[Legacy 98055]

One way of maintaining state is to store the date in a workbook name and then save the workbook...

This code goes into the workbook class.

Private Sub Workbook_Open()
    Call CheckForFiscalReset
End Sub

'run this little sub and then delete it
Sub RunOnceAndDelete()
    Me.Names.Add("FiscalResetDate", DateSerial(2009, 7, 1)).Visible = False
    Me.Save
End Sub

Sub CheckForFiscalReset()
    Dim FiscalDate As Date, HasRun As Boolean
    
    FiscalDate = [FiscalResetDate]
    
    If Date >= FiscalDate Then
        Me.Worksheets("Sheet1").Range("D1:D1").Delete Shift:=xlShiftToLeft
        FiscalDate = DateAdd("yyyy", 1, FiscalDate)
        Me.Names.Add("FiscalResetDate", FiscalDate).Visible = False
        Me.Save
    End If
End Sub

 

[MRA]

Thanks a lot!
It took me a bit to wrap my head around that code, but it looks like just what I needed.
I appreciate the help

Mike