Macro triggered by date

[Lonpuz]

At the beginning of each month, I need to copy/paste value over a number of
formulas so the results become fixed. I know how to automate the process
using the recorder, but want it to do it automtically when the 1st of the
month comes. I'm sure I can do that by making reference to a today()
formula, but don't know how to write the code. My apoligies if this was
already posted somewhere, as I couldn't find the exact info I was looking for.

Regards,

Lonpuz

 

[Ron de Bruin]

Hi Lonpuz

You can use the workbook open event in the thisworkbook module
http://www.rondebruin.nl/code.htm

Private Sub Workbook_Open()
If Day(Date) = 1 Then

' Your code

End If
End Sub

If you not open the file on the first day it will not run the code
If you open the file two times on day 1 then it will run two times so you must build in a few checks

 

[Bob Phillips]

If Day(Date) = 1 Then
Call myMacro
End If

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Lonpuz]

I will give that a try later today and let you know how it works. I will
assume that in your example, when it says "= 1", I can make a cell reference.

THANK YOU for your speedy replies!

Regards,

Lonpuz

 

[Lonpuz]

Tried to setup the month of February. Here is my macro;

Private Sub Workbook_Open()
If Day(Date) = 39479 Then
' FixFeb Macro
' Macro recorded 18/01/2008 Fixes Feb formula to values
'

'
Sheets("Feb Accounts").Select
ActiveWindow.SmallScroll Down:=-27
Range("D9177").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("F9:F177").Select
Range("F177").Activate
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("G9:H9").Select
End Sub

Basically I want to tell it that if it is Feb 1, to copy paste two columns
of data as values. That's it.
With this macro, I get an error. It says"Compile error: Block If without
End If"
Any ideas?