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”.

 

[davesexcel]

I think you stopped typing before asking the question.

 

[Yongle]

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.

Run the macro below and look in VBA Immediate window - display that window with {CTRL} G when in VBA editor

Sub IsThisCorrect()

    Dim DateString As String, CreateThisFolder, SaveFileWithThisName, FullSaveName
    Const DefaultPath = "C:\Folder\Subfolder"
    Const DefaultName = "Name of Workbook "

    DateString = Format(Date + 6 - WorksheetFunction.Weekday(Date, 3), "MM DD YY")
    CreateThisFolder = DefaultPath & "\" & DateString
    SaveFileWithThisName = DefaultName & DateString & ".xlsm"
    FullSaveName = CreateThisFolder & "\" & SaveFileWithThisName
   
    Debug.Print "Folder:", CreateThisFolder
    Debug.Print "File:", SaveFileWithThisName
    Debug.Print "Path:", FullSaveName

End Sub

Are the created strings returning the correct workbook name, subfolder and save to path ?

 

[newshound12]

Thanks for your reply.
I see the code in the immediate window.
I can make adjustments to suit my needs.
How do I get the code to work with the workbook?

 

[Yongle]

Simply amend the default path string and default name string to those you require
WARNING - The default path must already exist, otherwise the code will fail

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

    DateString = Format(Date + 6 - WorksheetFunction.Weekday(Date, 3), "MM DD YY")
    CreateThisFolder = DefaultPath & "\" & DateString
    SaveFileWithThisName = DefaultName & DateString & ".xlsm"
    FullSaveName = CreateThisFolder & "\" & SaveFileWithThisName 
'create folder
    On Error Resume Next
        MkDir CreateThisFolder
    On Error GoTo 0
'save under new name
    ThisWorkbook.SaveAs FullSaveName
End Sub

Above is the code previously supplied but updated to create a folder and save the file
If, for any reason, the code does not work when you test it, please post your COMPLETE amended code
- click on the </> icon above post window
- paste the code into the window which appears
- set Language to VBA code
- click on Continue.

 

[Yongle]

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.

You want 6 copies of the workbook created with 6 different names - what is the required name string ?
my guess is
default name(as above) + SundayDateString(as above) + Monday + extension .xlsx
ie something like this ...
Name of Workbook 12 15 19 Monday.xlsx

 

[Yongle]

When closing a workbook, automatically save it as

We can automate after you have confirmed that code in post#5 is doing EXACTLY what it should!

 

[newshound12]

After creating a folder and subfolder, your code works for my needs.
Now, how to automate?

 

[Yongle]

To test the code beow
- place code in ThisWorkbook Module
- close the workbook with X in top right corner
- message appears
- answer yes to run the macro,


The VBA goes in ThisWorkbook code module - does not work anywhere else

Simply call the other macro from Workbook_BeforeClose like this

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If MsgBox("Create weekly files?", vbYesNo, "Weekly Job") = vbYes Then
        Call SaveSunday
    End If
End Sub

 

[newshound12]

Thanks for your help. So far working fine.
Now how to get daily saved files like "12 15 19 Monday.xlsx"