VBA to find next Monday

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

What is the best way to find the date for next monday, and return the value
in as part of the file name when saving?
 
Hello Byron,

Here is a VBA macro to Return the next Monday from today.


Code:
--------------------

Public Function NextMonday() As Date

Dim D As Integer
Dim N As Date

D = Weekday(Now)
N = Now() + (9 - D)

NextMonday = N

End Function
 
I'm not sure just how you wanted to incorporate the date into the file name
for the saved workbook but something like this might get you close.

Sub NameAsNextMon()
Dim K As Integer
Dim dteMon As Date
Dim tempName As Variant

K = Weekday(Now)
dteMon = Now() + (9 - K)
tempName = Year(dteMon) & "-" & Month(dteMon) & "-" & Day(dteMon) & ".xls"
Do
fName = Application.GetSaveAsFilename(tempName)
Loop Until fName <> False
ActiveWorkbook.SaveAs Filename:=fName
End Sub


Steve Yandl
 
Back
Top