Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro triggered by date
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro triggered by date
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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Lonpuz" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro triggered by date
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro triggered by date
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro triggered by date
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("D9:D177").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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
referring to the button that triggered the macro | Excel Programming | |||
Automatic copy triggered by change in date | Excel Worksheet Functions | |||
Macro triggered by an event | Excel Programming | |||
Can a macro be triggered when a cell is selected? | Excel Programming | |||
Macro triggered by an event | Excel Discussion (Misc queries) |