WorksheetFunction.Month error with object doesn't support property or method

user_1046884 0
Viewed 170

I am trying to save a file into folder & sub folder that has dynamic names.

Structure of my folder & sub folder is

Shared Drive -> Main -> Year (i.e. 2018) -> Month (i.e. 09 September).

I wish to save in last month's folder since data comes from last month. I want to save the file name as PIF_09.30.2018 so using end of last month's dates.

I am getting an error

object doesn't support this property or method.

Sub Save_wkb()
Dim Path As String
ActiveWorkbook.SaveAs "S:\Main\" & Format(Now(), "YYYY") & _
     Application.PathSeparator & Format(WorksheetFunction.Month(Now, -1), "MM ") & _
   Format(WorksheetFunction.Month(Now, -1), "MMMM") & _
   Application.PathSeparator & "PIF_" & _
   Format(WorksheetFunction.EoMonth(Now, -1), "MM.D.YYYY") & ".xlsx", _
   FileFormat:=51
End Sub
vba excel
1 Answers

You are not able to use WorksheetFunction.Month - it doesn't exist.

Since you are trying to subtract a month, you can use the DateAdd() function instead.

Dim Path As String
Path = "S:\Main\" & Format(Now(), "YYYY") & Application.PathSeparator & _
    Format(DateAdd("m", -1, Date), "00 ") & Application.PathSeparator & _
    Format(DateAdd("m", -1, Date), "MMMM") & Application.PathSeparator & _
    "PIF_" & Format(WorksheetFunction.EoMonth(Now, -1), "M.D.YYYY") & ".xlsx"
user_57817450
Related