How to loop through workbooks in a folder and import data to a master workbook VBA?

[Bobstar]

Hi all

I am desperately looking for help to automate my work. I am looking for help with a code that can do the following:

1. Open a folder
2. Loop through multiple workbooks in that folder
3. For each workbook in that folder, import data contained in the "Summary" sheet to thisWorkbook (workbook containing the code) in sheet named "Data"

PS: The columns in the Summary worksheet are fixed (A:G) but the rows are variable depending on the workbook.

Your help is very much appreciated.

Thanks

 

[Bobstar]

You are very well come
And thank you for the feedback
Be happy

Hi. Thanks for your earlier help.
I am wondering whether your code could be expanded to do the following:

1. The workbooks in the folder are labelled by dates for example “17/10/2020 workbook 1.”I would like vba to get the newest workbook based on date.
2. Copy the data from the newest workbook in a separate sheet. Lets call it “newest data”.

 

[mohadin]

FTI “17/10/2020 workbook 1.”
File name can not contain some character , "/" one of them

 

[Bobstar]

FTI “17/10/2020 workbook 1.”
File name can not contain some character , "/" one of them

You are right. The file names are in this format “17102020 Workbook1”. I could rename them in a manner vba can identify which is the newest date. Any ideas?

 

[mohadin]

this Link might help

vba open the most recent file in a folder

There are some exel files in a Folder. The aim is to search for the file with the highest date (in the Format: Fundings " & Format(LMD, "DDMMYY") & ".xls) and open it. E.g. the file names are

stackoverflow.com

 

[Bobstar]

this Link might help

vba open the most recent file in a folder

There are some exel files in a Folder. The aim is to search for the file with the highest date (in the Format: Fundings " & Format(LMD, "DDMMYY") & ".xls) and open it. E.g. the file names are

stackoverflow.com

Thanks alot. That is exactly what I was looking for.