AutoRun Macro

[chrisnsmith]

I have the following macro for my spreadsheet along with one cell designated
for the Taday() function. How do I set this macro to autorun when the date
changes?

Sub CustomerFutures()
'
' CustomerFutures Macro
'
'
Range("H9:I31").Select
Selection.Copy
Range("D9:E31").Select
ActiveSheet.Paste
Range("F9:I31").Select
Application.CutCopyMode = False
Selection.ClearContents
End Sub

 

[Don Guillett]

This should do it. Now you need to tell us when you want it to fire. If you
want it to fire when the file opened put in the ThisWorkbook module
Autpen. But that would assume the changes had been made. Perhaps you want
to put in the Before_close.

Sub runifdatechg()
If Range("c1") < Date Then
Range("H9:I31").Copy Range("D9:E31")
Range("F9:I31").ClearContents
Range("c1") = Date
End If
End Sub

 

[chrisnsmith]

I'm new at Excel so please bare with me. I simply want the macro to run when
the date in cell K2 changes and at no other time. Can this be done without
actually being in the Excel program, in other words, overnight while the
computer is idle?

 

[Shane Devenshire]

Hi,

In the ThisWorkbook object you could consider something like this

Private Sub Workbook_Open()
Application.OnTime TimeValue("00:00:00"), "my_Procedure"
End Sub

This is your code in a regular module.

Public Sub my_Procedure()
you code
End Sub

However, Excel must be running for this to trigger at the desired time. A
computer that is in sleep mode will not run the procedure at the desired
time. If you then wake the computer up I don't know if it will automatically
run past stuff or not, you will need to check that.

 

[chrisnsmith]

As I said before, I'm new at this so it took me awhile to realized your code
would only run on the initial opening of my workbook. I inserted it in a
module and it worked great. Thanks.

 

[Don Guillett]

Glad you got it together