MsgBox auto populate problem

[alhbeu]

I have this code which autosaves a file to a certain location, however it automatically saves it with today's date
Dim filename As String
Dim location As String
Save = MsgBox("EXPORT?", vbYesNo, "EXPORT")
If Save = vbYes Then
filename = "sample name" & Format(Date, "yyyy_mm_dd") & "_EMEA.csv"

However it actually needs to be saved with the last Friday's date. How can I change it so I can either enter just the date part of the name into the message box, or just to manually enter in the name of the file each time??

Any help would be greatly appreciated!

 

[Airfix9]

Sub Test()

    Dim filename As String
    Dim location As String, i As Integer
    
    Select Case Format(Date, "ddd")
        Case "Sun"
            i = 2
        Case "Mon"
            i = 3
        Case "Tue"
            i = 4
        Case "Wed"
            i = 5
        Case "Thu"
            i = 6
        Case "Fri"   'change i to 0 if you want to show today's date if it is Friday
            i = 7
        Case "Sat"
            i = 1
    End Select
    
    Save = MsgBox("EXPORT?", vbYesNo, "EXPORT")
    If Save = vbYes Then
    filename = "sample name" & Format(Date - i, "yyyy_mm_dd") & "_EMEA.csv"

 

[JamesW]

Hi,

Try:

    Dim filename As String
    Dim location As String
    Dim x As Integer

    Save = MsgBox("EXPORT?", vbYesNo, "EXPORT")
    If Save = vbYes Then
        x = Weekday(Date)
        If x = 7 Then
            lfDate = Date - 1
        Else
            lfDate = Date - 1 - x
        End If
        
        filename = "sample name" & Format(lfDate, "yyyy_mm_dd") & "_EMEA.csv"
        ActiveWorkbook.SaveAs filename
    End If

 

[alhbeu]

The file is run on Tuesdays, and the date needs to be for the Friday before - will the numbers assigned to the days work in this case?

 

[Airfix9]

My one will work for whatever day you run it on.

 

[alhbeu]

many thanks, this works perfectly