Copy data from workbooks in a folder to one master workbook

[jayn309]

Hi. I have data in multiple workbooks in a folder with the same template and I need to extract those circled data into one master workbook as below

I have a code to loop thru all the workbooks in the folder but I do not know how to do the data copying/extracting. Thank you for any help!.

Sub LoopAllFilesInAFolder()

'Loop through all files in a folder
Dim fileName As Variant
fileName = Dir("C:\Users\Student\Documents\6004*")

While fileName <> ""
    
    'Insert the actions to be performed on each file
    
Wend

End Sub

 

[jayn309]

Check to make sure that all the file names start with "600" and have an "xlsx" extension.

I tested with some directories directly to some subfolders, it scanned through all of them and copied the data. But if I go back to the folder of those subfolders, it only scan 1 random subfolder. I check all the files and their names start with "600" with xlsx extension. Would the number of files be the issue if there are too many of them?

 

[mumps]

Would the number of files be the issue if there are too many of them?

That should not be a problem. Try replacing this line of code:

If File.Name Like "600*.xlsx"

with this one:

If File.Name Like "*600*.xlsx"

 

[jayn309]

That should not be a problem. Try replacing this line of code:

If File.Name Like "600*.xlsx"

with this one:

If File.Name Like "*600*.xlsx"

Hi. Some files got opened and closed really quick during the process but the issue still remains.

 

[mumps]

Please upload 2 or three of the files that are not being opened and post the links here.

 

[jayn309]

Please upload 2 or three of the files that are not being opened and post the links here.

Hi! I will just go with your previous to scan each level of folders instead since it's too mush to go through all levels and files! Thank you so much for your help.

 

[mumps]

My pleasure.