Combine multiple sheets with same name from seperate wbs in a folder into one workbook

Hello All,

I want to combine a specific tab ("Summation") in all workbooks in a folder to a single workbook. I have my macro asking for the file path, a file name pattern and the tab name from each workbook. The only 2 I really need are the file path and the tab name but the middle is just something that could be useful in future use.

The only tab that is in the folder that is running the macro is "SumMaster" which also has a macro that compiles the numbers I need once all the sheets exist in a single workbook. I have roughly 200 files and this process has to be repeated yearly, so I am trying to automate it as much as possible.

I have a code to combine the workbooks but it bombs out [Runtime Error '9'] at

Please Login or Register  to view this content.

I have tried included On Error Resume Next but that just causes Excel to completely freeze and requires a restart.

If anyone has any suggestions on how to optimize or if they have another code that would work better, please let me know.
I have included the entire code below.

Please Login or Register  to view this content.