VBA excel code - to update spreadsheet based on the date

[XYZklmn]

Hi All maestros of excel VBA,

I am writing here to receive maybe some help, advise how to write a right code.

Given: I have my excel macro sheet .xlsm in which i am trying to pull data from the archive folder based on the date.
For example in .xlsm file in the cell B1 I have a date. In archive folder i saved .xlsx sheets as per one date, each sheet contains date in B1 cell. The name of .xlsx sheet is also a date.
The range of data to be copied from .xlsx files to .xlsm is the same, i.e. (C6:I6), the number of lines can be different each day (but not dramatically different). What would be the best code to write to execute the steps?

PS. I apologize for my ignorance if someone already posted similar question, just couldn't find it.
I received the below code from a friend, but it doesn't work (will share as example):

Sub aggr()
Application.ScreenUpdating = False
Application.AskToUpdateLinks = False

Dim Myname As String
Dim Mypath As String
Dim sPath As String
Dim WB As Workbook
Dim sh As Worksheet
Dim ra As Range
Dim coll As Collection
Dim i As Integer
i = 1
Mypath = ThisWorkbook.Path
Set coll = (Mypath, "*07012020.xls*", 1)

For Each Filename In coll
Excel.Application.Workbooks.Open Filename
Sheets("07012020").Select
Sheets("07012020").Range("C5", "I5").Select
Selection.Copy
ActiveWorkbook.Close False: DoEvents
ThisWorkbook.Sheets("STATIC").Cells(1, i).Select
ActiveSheet.Paste
i = i + 1
Next


End Sub


Regards,
X

 

[Rijnsent]

Hi XYZklmn,
to check: your code should pull in only one file when you run the macro (versus: loop through the files and open e.g. all files between two dates)? And the results should always be pasted on STATIC in A1? And how can the number of lines in the xlsx file be different? It could be that you'd need to copy C5:I10 -> if so, how would you know you'd have to copy 5 lines?
Thanks for a bit more clarification (and placing your code in VBA tags next time).
Koen