VBA Code that backups when opening worksheet

[Alexander K]

Hi

I have written a VBA code that backups the excel worksheet on the 3rd monday of the month but it doesnt work and i doing something wrong.

Private Sub Workbook_Open()
'Backup

If TODAY() = (TODAY() - Day(TODAY()) + 32 - Weekday(TODAY() - Day(TODAY()) - 1)) Then
            
        ChDir _
        "L:\LOG20 Spares Planning\Van Stocks Control\Van Stock Profiles\Generic Regional Profiles\Profile Backups"
        ActiveWorkbook.SaveAs Filename:= _
        "L:\LOG20 Spares Planning\Van Stocks Control\Van Stock Profiles\Generic Regional Profiles\Profile Backups\300 STYLE GRP PROFILES B1.xlsm" _
        , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=True
    
    Else
End If


End Sub

Can anyone help

 

[Momentman]

In VBA, the equivalent for today() is date, so maybe change all in your code. I haven't checked hte formulas/logic but thats all I can see/say for now

Private Sub Workbook_Open()
    'Backup
    If Date = (Date - Day(Date) + 32 - Weekday(Date - Day(Date) - 1)) Then
            ChDir _
            "L:\LOG20 Spares Planning\Van Stocks Control\Van Stock Profiles\Generic Regional Profiles\Profile Backups"
            ActiveWorkbook.SaveAs Filename:= _
            "L:\LOG20 Spares Planning\Van Stocks Control\Van Stock Profiles\Generic Regional Profiles\Profile Backups\300 STYLE GRP PROFILES B1.xlsm" _
            , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=True
        Else
    End If
End Sub

 

[Alexander K]

That doesnt seem to work maybe i am not using the right code for VBA.

Basically every Monday i want it to make a backup, and when its made 4 backups it, starts again and saves over the first out of the four backups and so on.

So there is only ever 4 backups does that make sense?