automatically save a workbook

[newshound12]

When closing a workbook, automatically save it as an .xlsm workbook with next Sunday’s date (MM-DD-YY) in a folder created also with next Sunday’s date.
Also save an .xlsx copy of the workbook with the name of the day of the week (Monday thru Saturday, Sunday’s save will be the actual date) in the same folder.
The .xlsm can be overwritten after a message box asks “Do you want to overwrite existing file or rename it as a copy”.

 

[Yongle]

Q Is there a possibility that user would NOT want to overwrite the SUNDAY file but still want to create the Monday to Saturday files ?
If the answer to the above question is YES then we will need to modify the code slightly
- let me know after you have tested


To prevent files beingdisplayed out of sequence (Fri Mon Sat Thurs Tues Wed) in Windows Explorer ... each day is prefixed with a number
Array("1_Monday", "2_Tuesday", "3_Wednesday", "4_Thursday", "5_Friday", "6_Saturday")

If that is not what you want. ...replace with
Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")

Sub SaveSunday()
    Application.ScreenUpdating = False
    Dim DateString As String, CreateThisFolder As String, SaveFileWithThisName As String, FullSaveName As String, NewDay As Variant
    Const DefaultPath = "C:\Folder\Subfolder"
    Const DefaultName = "Name of Workbook "

    DateString = Format(Date + 6 - WorksheetFunction.Weekday(Date, 3), "MM DD YY")
    CreateThisFolder = DefaultPath & "\" & DateString
    On Error Resume Next
        MkDir CreateThisFolder
    On Error GoTo 0

'Sunday
    SaveFileWithThisName = DefaultName & DateString & ".xlsm"
    FullSaveName = CreateThisFolder & "\" & SaveFileWithThisName
    ThisWorkbook.SaveAs FullSaveName

'Monday to Saturday
    For Each NewDay In Array("1_Monday", "2_Tuesday", "3_Wednesday", "4_Thursday", "5_Friday", "6_Saturday")
        SaveFileWithThisName = DefaultName & DateString & " " & NewDay & ".xlsx"
        FullSaveName = CreateThisFolder & "\" & SaveFileWithThisName
        Application.DisplayAlerts = False
            ThisWorkbook.SaveAs FullSaveName, FileFormat:=xlOpenXMLWorkbook
        Application.DisplayAlerts = False
    Next NewDay
End Sub

 

[Yongle]

Now how to get daily saved files like "12 15 19 Monday.xlsx"

I was not sure what you wanted ..

So I went with Name of Workbook 12 15 19 Monday.xlsx
SaveFileWithThisName = DefaultName & DateString & " " & NewDay & ".xlsx"

But If you want 12 15 19 Monday.xlsx
replace with
SaveFileWithThisName = NewDay & ".xlsx"

 

[newshound12]

Thanks for all your help. I have all the information I need now to make my modifications.