VBA code modification to copy data from a folder when name matches

[sudhansu121]

Hi Experts,
Hope you all are safe and doing good.

I need a heling hand making my code more dynamic so that I do not have to click and choose files one by one.

I have a folder where I place multiple reports . I also have a Master sheet, where I have my code and want to copy data to the sheet name "Data" in my master sheet from the other files placed in that folder.

Lets say I have a Report named as "Query" placed in the folder. I will run the VBA and when filename = Query, then it will copy data from that report and paste it in Master workbook sheet "Data".

Please note all the reports placed in folder will have only 1 sheet, but the names might not be the same. So we can take the sheet number instead so that I do not have to be worried about the names.

So my goal is, if I have 5 files then i should just click the macro only once and the it will search for the file name, if matches copy the data and paste.

Please Login or Register  to view this content.

[sudhansu121]

Can someone please help me on this ?

[AlphaFrog]

Try something like this. Change the path and base file name "*Query*" to suit.

Please Login or Register  to view this content.

[sudhansu121]

Try something like this. Change the path and base file name "*Query*" to suit.

Please Login or Register  to view this content.

Thanks a lot AlphaFrog for your help. I am yet to try the code,but the only thing what I see here is I have to give the folder path. And if somebody else wants to use this macro sheet then the folder path has to be provided again. So want keep it more dynamic so that use can select their own folders. Any suggestions with this? And how do I add multiple files ? So when the name match query and macro does its work should move to the next file and do it's work

[AlphaFrog]

No need to quote my code.

Below will prompt the user to select a folder and then loop through all the files in that folder that contain "query" in the file name.

Please Login or Register  to view this content.

[sudhansu121]

Hi AlphaFrog ,
Thanks and sorry for the quote (I replied from my moblie )

I tried the new code now, but it copied data and placed from 16th row and not from the 1st row