Run Code Weekly

Jaye7

Well-known Member
Joined
Jul 7, 2010
Messages
1,056
Can someone please provide a script that I can use so that my macro will run once a week, my macro is named "Copy_Files2"

Obviously if I didn't open the workbook for 10 days it would need to run the code automatically as 7 days had passed.

It is used so that each week it copies some files and deletes others, I have found scripts that will run a process daily but not weekly.

Thanks.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,138
Office Version
2013
Platform
Windows
Hi
try
This will run the macro at 2:30pm on Friday. I think the workbook will have to be open at the time though...and others may be able to improve on that
You could look here for using a closed workbook
Code:
http://www.ozgrid.com/forum/showthread.php?t=78499
Code:
Sub FriRun() 
    If Weekday(Now()) = 6 Then 
        Application.OnTime TimeValue("14:30:00"), "Copy_Files2" 
    End If 
     
End Sub 
 
Sub Copy_Files2() 
     
     ' your code goes here
     
End Sub
 
Last edited:

rattman

New Member
Joined
Nov 3, 2006
Messages
32
I am at work so a long reply is not possible. I use the CONTROL PANEL/SCHEDULED TASK to run a spreadsheet at a specific time and day. Just browse to the Excel sheet and pick it as a task. Then in the spreadsheet use the macro auto_open and put the code you need before the sheet is saved.

Using this method the sheet is not even open. I also use the ONHOUR command but that requires the sheet to be open. It works well when a sheet is gathering information and then you want to clean the data and pass it on to a different database.
Hope that helps,
If not I can be more descriptive.
rattman
 

Ruddles

Well-known Member
Joined
Aug 24, 2010
Messages
5,773
Another vote for Rattman's suggestion. I use this method and it works perfectly because you're not relying on Excel being active and you don't need to write any code to handle the scheduling, missed events, etc.
 

Jaye7

Well-known Member
Joined
Jul 7, 2010
Messages
1,056
Thanks for your help guys, I haven't ever used the control panel method before and now that I know about it it has some great possibilities for the future.:):):)
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,138
Office Version
2013
Platform
Windows
Yeah, nice call Rattman.
I haven't used it before, so I'll have to give it a whirl.
 

Forum statistics

Threads
1,081,518
Messages
5,359,236
Members
400,523
Latest member
ExcelNewbie98

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top