Dynamic Formula to Factor in the Weekend

Question

Hi I have this VBA code which, for each day, opens a file from the day before and copies in the data into rows, then there is another macro that then saves that in another cell that has yesterdays date in the cell in column A.

What i dont know how to set up is a concise if formula that says if its Monday then open up Sunday's, Saturday's and Friday's Files and saves them in the right place does anyone know how to amend the below two codes to make that happen?

Dim MonthYearString As String
MonthYearString = Format((Date - 1), "mmm yyyy")

Dim DateString As String
DateString = Format((Date - 1), "dd-mm-yyyy")

Dim DateStringFS As String
 DateStringFS = Format((Date - 1), "dd.mm.yy")
 
 
' Initialize the variable directory. We use the Dir function to find the first *.xl?? file stored in this directory.
directory = "C:\Users\XXXX-XXXX\Dropbox (SHG)\Alteryx Output\Daily Finance & Revenue Data\1. Daily Occupancy Output\IYO-Indigo York\" & YearString & "\" & MonthYearString & "\" & DateString & "\"
fileName = Dir(directory & "*manager*.csv")

' The variable fileName now holds the name of the first Excel file found in the directory. Add a Do While Loop.
Do While fileName <> ""

        ' There is no simple way to copy worksheets from closed Excel files. Therefore we open the Excel file.
        Workbooks.Open (directory & fileName), local:=True
        
        
        ' Import the sheets from the Excel file into import-sheet.xls.
        
        For Each sheet In Workbooks(fileName).Worksheets
            total = Workbooks("YORYK Daily Report " & DateStringFS & ".xlsb").Worksheets.Count
            Windows(fileName).Activate
            Rows("1:2").Select
            Selection.Copy
            Windows("YORYK Daily Report " & DateStringFS & ".xlsb").Activate
            Sheets("Actual data").Activate
            Rows("4:5").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
            Application.CutCopyMode = False
             
        Next sheet
        'Explanation: the variable total holds track of the total number of worksheets of Donors_TaxReceipts.xls.
        'We use the Copy method of the Worksheet object to copy each worksheet and paste it after the last worksheet of import-sheets.xls.
        
        'Close the Excel file.
       
        
        'The Dir function is a special function. To get the other Excel files, you can use the Dir function again with no arguments.
        fileName = Dir()
        
        'Note: When no more file names match, the Dir function returns a zero-length string ("").
        'As a result, Excel VBA will leave the Do While loop.

Loop

'Turn on screen updating and displaying alerts again (outside the loop).
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub




Sub test()
  Range("H8:M8").Select
  Selection.Copy
  
 For Each Cell In Range("A1:A5")
    If Cell.Value = (Date - 1) Then
    Cell.Offset(0, 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    
    End If
    
    Next Cell
    
End Sub