How can I update weekly data in VBA?

Question

I have to create a weekly performance overview of machines. The data is saved outside of the weekly Excel workbook in 12 workbooks, one for each month of the year.

To reduce the data I only present the data of the week with lookup functions depending on the dates.

• The first problem is that I have to manually update the data (e.g. november 21 -> december 21: ...\machine-data\21-11.xls -> ...\machine-data\21-12.xls).
  To update the data I use a Replace function in a macro to update the files but as you can imagine it takes too long (range is about 1000 cells).

• The other problem is that some weeks belong to two months so I can't use the Excel built-in refresh button for each month.
  

This is the actual VBA code (for the simplification in the picture):

Sub updateMonday()
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With
    
    With ThisWorkbook.Worksheets("sheet1")
        Range("C3:C4").Replace Cells(7, 3), Cells(8, 3)
    End With
    
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
    End With
End Sub

I created a macro for every day of a week.

The best-case would be a VBA function which knows which weekdays to update and is much faster than my code. Is it possible to work with arrays?