looping year auto script

[austin350s10]

I have a macro I would like my workbook to perform automatically at the beginning of each year. I know how to write something simple like do this if the date is greater than X but that only works for one year.

How can I make it so that when its done running the macro it changes the code to look for the next year so it will only run the code again when the following year comes along?

 

[Mr_Roscoe]

had you thought about evaluating only based on the day and month and ignore the year?

the VBA would then be something like ....where day = "01" And month = "01" Then...
which would run every 1st January

 

[austin350s10]

wow....that is pretty darn simple. Just for clarification what the actual script look like?.....im kinda new at this

Thanks BTW

 

[Mr_Roscoe]

austin - there will be a more elegant way to do this, but the following works:

Sub test()

Dim day As Integer, month As Integer

day = Application.WorksheetFunction.Text(Date, "dd")
month = Application.WorksheetFunction.Text(Date, "mm")

If day = "01" And month = "01" Then
MsgBox ("The macro has worked")
End If

End Sub

I tested changing the above to day = "01" to "31" and month = "08" and only then will the msgbox work. So just substitute your code that needs to run instead of the msgbox line.

HTH, Ian R.

 

[austin350s10]

Thanks again - that is exactly what i am looking for!!!

 

[austin350s10]

One potential problem with the above script. It would only run on 01/01. If the user happens to open the workbook on 01/02 the script will not work. I slept on this one and think I may have come up with something that will work a little better, but cant quite script it. Let me know what you think. My code is probably messed up as all get out, but maybe you could help me clean it up.

[B]Sub test()

Dim CurrentYear As Integer
Dim NextYear As Integer

CurrentYear = Application.WorksheetFunction.Text(Date, "yyyy")
NextYear = Cells(D6).Value  'I would manually type next year in D6 the first time "Lets Say 2011"


If CurrentYear =  NextYear Then
MsgBox ("The macro has worked")

Cells(D6).Value = Value+1 'I know this part is wrong but in theory it should change the original value of 2011 to 2012

End If

End Sub[/B]

 

[Mr_Roscoe]

Austin - I can see theproblem also, I assumed it would be a file that was used very day.

I can see what you are doing, check to see if the new year has clocked over, then run the macro as well as change the year date so it will be set for the next year.

However, my first thought was evaluating the save date of the xl file. If the save date is < 01 Jan and the day you open the file is > 1 Jan then run the macro. As long as the file is saved the macro will not run again until the foloowing 1 Jan.

Either option should work.

 

[Mr_Roscoe]

Austin - below is the VBA for either option. Please note it needs to be a macro that runs when the file is opened.

Sub Austin_test()

Dim CurrentYear As Integer
Dim NextYear As Integer

CurrentYear = WorksheetFunction.Text(Date, "yyyy")
NextYear = Range("D6").Value 'next year is in D6 the first time "Lets Say 2011"

If CurrentYear = NextYear Then
MsgBox ("The macro has worked")
Range("D6").Value = NextYear + 1 'This changes the original value of 2011 to 2012
End If

End Sub

Sub Mr_Roscoe_test()

Dim CurrentYear As Integer
Dim FileYear As Integer

CurrentYear = WorksheetFunction.Text(Date, "yyyy")
FileYear = WorksheetFunction.Text(ThisWorkbook.BuiltinDocumentProperties("last save time").Value, "yyyy")

If CurrentYear > FileYear Then
MsgBox ("The macro has worked")
End If

End Sub

 

[austin350s10]

Thanks again. I think that will work. I'll test it out and get back to you.

 

[Mr_Roscoe]

sorry, one last thing, name the macro Sub Autpen() so that it automatically runs when the file is opened. cheers, Ian R.