CODEWITHSUNDEEP
excelvba
Matt Puck
Matt Puck

Reputation: 3

VBA for saving worksheet with dynamic date

I want to save an excel workbook at the end of the macro with a dynamic date in the file name. I want the name to contain the previous month. For instance, if the macro is run on 8/9/2013 I want the file to save with 7/2013 specified. This is what I have and it works great if I use the current month. But, if I ran this on 8/9/2013 I would want the file to save as "Monthly Report 2013-07".

ActiveWorkbook.SaveAs "\\FULL PATH\Monthly Report " & Format(Date, "yyyy-mm") & ".xls"

End Sub

I've also used something like this previously, but couldn't get this to subtract a month either:

wbNam = "Monthly Report_"
dt = Format(CStr(Now), "yyyy_mm")
ActiveWorkbook.SaveAs Filename:= wbNam & dt
End Sub

Thanks!

Upvotes: 0

Views: 10258

Answers (2)

Joe
Joe

Reputation: 6827

Try: 

dt = Format(DateAdd("m", -1, Now), "yyyy_mm")

Upvotes: 1

user2668920
user2668920

Reputation: 64

Just use this function

Public Function PreviousMonth(ByVal d As Date) As Date
PreviousMonth = DateSerial(Year(d), Month(d) - 1, Day(d))
End Function

DateSerial is smart enough to support zero or negative month numbers. In other words PreviousMonth(#1/1/2000#) is December 1st 1999.

Upvotes: 1

Related Questions