Open file of last day of previous month

Hi GUys

I have the below code, but i want to amend it so that it opens the last file of the previous month - i.e. in this case for example it would be 31/08/2016 - however it is generating the 1/09/1016 file:

If Month(Date) = 1 Then


FileYear = Year(Date) - 1

strFileMonth = Format((DateSerial(Year(Date), Month(Date), 0)), "YYYYMM")

FileDate = Format(Date - 1, "ddmmyyyy")

Filepath = "Z:\DailyReports\" & FileYear & "\" & strFileMonth & "\*timedeposit*.xls "


Else



FileYear = Year(Date)

strFileMonth = Format(DateAdd("m", 0, Now), "YYYYMM")

FileDate = Format(Date - 1, "dd.mm.yyyy")


Filepath = "Z:\DailyReports\" & FileYear & "\" & strFileMonth & "\*timedeposit*.xls "


End If



Workbooks.Open (Filepath)

ActiveWorkbook.SaveAs "L:\Macro TestTest.xls"

Application.DisplayAlerts = False

End Sub

I understand I need to change the -1 to zero but it doesnt seem to work?

Freddie