Help with VBA Macro

[Sunny1290]

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]

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]

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

[SamT]

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]

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]

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!!!!