VBA: Saving a copy of a workbook

[jimmy2times]

Hello,

I have the following code

Sub CreateBackup()
'Step 1: Save workbook with new filename


ThisWorkbook.SaveCopyAs _
Filename:=ThisWorkbook.Path & "" & _
Format(Date, "mm-dd-yy") & " " & _
ThisWorkbook.Name


End Sub

The code is working correctly. However, I want to always save the workbook with monday's date from last week rather than today's date. So for example if I was running the code today I want to save the date as 19th November.

Is there a way I can get this code to save to the required date?

I am quite new to VBA so any help is appreciated.

Thank you

 

[John_w]

Try this:

Sub CreateBackup()
    'Step 1: Save workbook with new filename
    
    ThisWorkbook.SaveCopyAs fileName:=ThisWorkbook.Path & "\" & Format(Date - Weekday(Date, vbMonday) - 6, "mm-dd-yy") & " " & ThisWorkbook.Name

End Sub

 

[jimmy2times]

This works great.

Thank you for taking the time to help me.