Save File With Previous 'Month' In FileName

[Small Paul]

Hi All

I have been working on opening/saving files using previous day.
I now need to 'save' a workbook with previous month in the filename.
The code I have is based on what has worked before:

    Sheets("Monthly CAC Report").Select    ActiveWorkbook.Names.Add Name:="Locator", RefersToR1C1:= _
        "='Monthly CAC Report'!R524288C8192"
    Sheets("Monthly CAC Report").Select
    Sheets("Monthly CAC Report").Copy
    ChDir "Z:\Promotional and marketing\Product Performance\Monthly Data"
    ActiveWorkbook.SaveAs Filename:= _
        "Z:\Promotional and marketing\Product Performance\Monthly Data\CACS " & Format(Now() - 1, "MMM-YYYY") & " PS Test.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End Sub

Unfortunately, this saved the file as "CACS Jul-2018 PS Test"

I was hoping that, as I am using format MMM-YYYY it would save as 'June 2018'.

Any thoughts would be appreciated.

Cheers
Small Paul.

 

[footoo]

Format(DateAdd("M", -1, Now), "MMMM YYYY")

Have a look here : https://www.google.com.hk/search?sa.....0...1c.1.64.psy-ab..0.1.91....0.EMprQV0T2TI

 

[Small Paul]

footoo
many thanks for your help