run a macro at condition

[dikken20]

Hi,

I have a macro.
I want it to be run automatically at a specific time, but not every day but only on days that are not holidays.

Additional available values I have on my sheet that may help:

1. I have a cell in my sheet that indicates me whether today is an holiday or not (If today is a holiday then this cell's value turn to "Holiday", if not, its value is "WorkingDay").

2. I have a cell that tells me the exact time each second automatically.


Any ideas how to make this happened ?

Thank you in advance!
Dikken.

 

[Arcticwarrio]

what does the macro do and can it be triggered another way eg onSave

 

[dikken20]

The macro is pretty simple:

It copies column D into column E, then copies column C in to column D, then B into C.

I tried triggeting by Ontime but it didn't work...

Therefore, there must be a simple way to do this, I just don't know how and need help on this

 

[Arcticwarrio]

do you open it each day?

you may be able to use the on Open trigger?

 

[AlphaFrog]

If you do a web search for something like; Excel VBA Application.OnTime, you will find several step-by-step guides on how to set up a macro to run at a specific time every day. Here's one: How to Run a Macro at a Set Time

In your daily run macro, you could have something like this to have it "do something" only on Working Days.

Sub Daily_Macro()
    If Sheets("Sheet1").Range("A1").Value = "WorkingDay" Then
        [COLOR="Green"]'Do somthing here or call another macro[/COLOR]
    End If
End Sub

 

[dikken20]

Thanks for your replies guys,

Arcticwarrio,
The file is opened all the time. Since I'm away from my PC very often, I want this to be done automatically without my need to open it or do something else. plus, each time the files been open - I need to enable the macro.


AlphaFrog,
I've done something like your suggested a while ago after reading bout that on the net. It really worked but only for the same day I wrote it, the day after... it stopped. This is the reason I asked bout some other possible solution.

 

[Arcticwarrio]

you can sign your project so it doesnt keep asking if you want to enable macros

 

[AlphaFrog]

It really worked but only for the same day I wrote it, the day after... it stopped. This is the reason I asked bout some other possible solution.

The daily macro needs to to initiate the next OnTime event so it will run again the next day.

Sub Daily_Macro()

    If Sheets("Sheet1").Range("A1").Value = "Holiday" Then
        'Do somthing here or call another macro
    End If
    
[COLOR="Red"]   Application.OnTime TimeValue("15:00:00"), "Daily_Macro"[/COLOR]
    
End Sub

Are you doing something like that in you actual macro?

 

[dikken20]

ArcticwarrioHow do I do that ?


AlphaFrogI think that now I've got the reason it ran only one time (and tell me if I'm wrong), because I need to initiate it each time after it run on that time, right ?
Well, I'll do what you've offered me, but I remember that after it didn't run that day (after the day it did) I "F5" it, shouldn't the method be initated then ?


Thanks again.

Edited: Hmm,.. did you edited your answer ? I can't see the entire code you wrote before..