Save file with previous month date

GreenyMcDuff

Active Member
Joined
Sep 20, 2010
Messages
313
Hi all,

I am using this code to save a WorkBook:

Code:
    ActiveWorkbook.SaveAs Filename:="H:\VBA Macros\Barnwell, Robin\Client Product Report " & [COLOR=red]Format[/COLOR](DateSerial(Year(Now), Month(Now()) - 1, 0), "yyyy-mm"), FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
            , CreateBackup:=False

I want the file name (if today's month is June) to be:

"Client Product Report 2011-May.xls"

When I compile the project the debugger highlights the word "Format" and says "Wrong number of arguments or invalid property assignment"

I assume I have syntaxed the Format incorrectly but after having scoured Google for quite sometime I can't find anything that suggests it is wrong.

Hopefully someone here can help

Thanks

Chris
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
This works for me

Code:
ActiveWorkbook.SaveAs Filename:="H:\VBA Macros\Barnwell, Robin\Client Product Report " & Format(DateSerial(Year(Date), Month(Date) - 1, 1), "yyyy-mm"), FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
            , CreateBackup:=False

You don't have a Sub called Format do you?
 
Upvote 0

Forum statistics

Threads
1,224,590
Messages
6,179,756
Members
452,940
Latest member
rootytrip

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top