VBA to find next Monday

[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?

 

[Leith Ross]

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

 

[Steve Yandl]

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