how can i save workbook as previous date in vba?

…deleted...

Try this.

Dim strName As String

Select Case Day(Now)
    Case 2 To 6 ' Tuesday to Saturday
        strName = Format(Now - 1, "mmmm d yyyy")
    Case 7 ' Sunday
        strName = Format(Now - 2, "mmmm d yyyy")
    Case 1 ' Monday
        strName = Format(Now - 3, "mmmm d yyyy")
End Select

Select allOpen in new window

sorry, my needs have changed. how do i save the workbook as the current date in the format similar to: "October 11 2013"

Thanks.

You'll need a macro, but if you save the workbook as an xlsx file all code including the macro will be deleted so you'll need to save it as an xls or xlsm file. This saves it as an xls with today's date.

      ActiveWorkbook.SaveAs Filename:=Application.Path & " " & Format(Now, "mmmm d yyyy") & ".xls", FileFormat:=xlExcel8, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False

Select allOpen in new window

But if you still want to subtract 1 then let me know.

good point. i will save as .xlsm.

where does this save the file? how can i designate the path?

Do you want to choose the path or do you want to hard-code it?

I should answer your question. The code above saves it to the application's current folder.

hardcode the path. right now i have no idea where its saving to. so yes, i want it hard coded.

thanks.

Sorry I was rushing. These have two small changes.

Save to current path

    ActiveWorkbook.SaveAs Filename:=Application.Path & "\" & Format(Now, "mmmm d yyyy") & ".xlsm", FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Save to some other place    

Change "C:\MyPath\" to whatever you want  
     
    ActiveWorkbook.SaveAs Filename:="C:\MyPath\" & Format(Now, "mmmm d yyyy") & ".xlsm", FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

nice work! thanks!

You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013