Create shared file copy and start timer

[jay_the_cob]

Hello,

I read through the forum questions, but cannot find something specific enough to my issue. I am trying, from a template file, to have a vba button that will save a new shared copy with today's date and have a timer start for deadlines. My codes work up to the "ActiveWorkbook.SaveAs Filename" where I need to debug. I am quite a beginner with VBA so any help is appreciated.


Sub SaveDailyFile()

Dim Msg As String, Ans As Variant

Msg = "You are about to save a daily copy of this oversight dashboard. Do you wish to proceed?'"

Ans = MsgBox(Msg, vbYesNo)

Select Case Ans

Case vbYes

If Not FileFolder("\\Mflccg02\Cash Admin\Montreal\Jacob\Management\DAILY FILE" & Format(Date, "YYYY")) Then
MkDir ("\\Mflccg02\Cash Admin\Montreal\Jacob\Management\DAILY FILE" & Format(Date, "YYYY"))
End If

If Not FileFolder("\\Mflccg02\Cash Admin\Montreal\Jacob\Management\DAILY FILE" & Format(Date, "YYYY") & "" & Format(Date, "MMMM YYYY")) Then
MkDir ("\\Mflccg02\Cash Admin\Montreal\Jacob\Management\DAILY FILE" & Format(Date, "YYYY") & "" & Format(Date, "MMMM YYYY"))
End If

ActiveWorkbook.SaveAs Filename:= _
"\\Mflccg02\Cash Admin\Montreal\Jacob\Management\DAILY FILE" & Format(Now(), "YYYY") & "" & Format(Now(), "MMMM YYYY") & "\Oversight Report - " & Format(Now(), "MM.DD.YYYY") & ".xlsm" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False, AccessMode:=xlShared

Dim SchedRecalc As Date

With Sheet1.Range("G1")

.Value = Format(Time, "hh:mm:ss AM/PM")

End With

Call SetTime

SchedRecalc = Now + TimeValue("00:00:10")

Application.OnTime SchedRecalc, "Recalc"

Case vbNo
GoTo Quit:

End Select

Quit:

End Sub



Thanks,

 

[Fazza]

hello,

Not sure exactly what you're doing. I wonder if SaveCopyAs might help? So, instead of SaveAs
https://msdn.microsoft.com/en-us/vba/excel-vba/articles/workbook-savecopyas-method-excel