View Full Version : Help with VBA Macro
Sunny1290
04-06-2021, 08:31 AM
Hi all,
apologises in advance for any mistakes but i am new here!
I have been tasked to create a macro which saves the current workbook and make a copy of it to another directory but I need to do automatically from Monday - Friday only?
Could someone point me in the correct direction to do so? I have figured out how to do this on a specific time but I am not sure how to incorporate the Mon-Fri only time frame?
Kindest,
Sunny
Paul_Hossler
04-06-2021, 09:18 AM
You mean any time that anyone saves the WB on a Mon-Fri a copy gets saved to another folder?
What is there's already a saved copy there?
Or you can use Weekday()
Option Explicit
Sub DayCheck()
If Weekday(Now) >= vbMonday And vbFriday <= Weekday(Now) Then
MsgBox "OK to save"
Else
MsgBox "No save for you today"
End If
End Sub
Sunny1290
04-06-2021, 09:35 AM
Hi Paul,
Thank you so much for replying.
I need the Macro to run automatically every day from Monday - Friday at X time.
This macro would copy this workbook to a different location and if there is a file there already it should not replace this file it should just make additional copies with a new file naming pattern.
Kindest,
Sunny
This assumes the workbook will be open at TimeToSave every week day
Option Explicit
Public Sub SaveOnTime()
'Runs CopyBookToFolder at SaveTime
Application.OnTime SaveTime, "CopyBookToFolder"
End Sub
Private Function SaveTime()
'Only called monday thru Friday
'Sets the date and time to save
Const TimeToSave = "16:30:00" 'Edit time to suit. Required to prevent Time part from creeping
Dim DayToSave As Date
'Moves Day to tommorow, except On Friday, moves it to Monday
If Weekday(Date) >= vbMonday And Weekday(Date) <= vbThursday Then
DayToSave = DateValue(DateAdd("d", 1, Date))
Else
DayToSave = DateValue(DateAdd("d", 3, Date))
End If
SaveTime = DayToSave + TimeValue(TimeToSave)
End Function
Private Sub CopyBookToFolder()
'Edit Path and Extension to suit. Adds Date (Saved) stamp to book Name
Me.SaveCopyAs "C:\TEMP\" & Me.Name & " - " & Format(Date, "yyyy/mm/dd") & "xlsm"
End Sub
Paul_Hossler
04-06-2021, 11:23 AM
A macro only runs if Excel is open
You could write a Windows Scripting Host (.VBS) script to copy the Excel file with a filename that includes the timestamp
Add it as a scheduled task to run on x time Mon thru Fir
Sunny1290
04-08-2021, 06:07 AM
Just wanted to say thank SamT and Paul for your help on this.
You guys have been such a great help in my limited requeiments.
Your assistance hit the nail on the head and I ironed out a few changes and additional things.
Really appreciate it!!!!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.