Combining Files with Different Schemas in Excel with Multiple Worksheets

[Steph23]

Hi there! I'm trying to combine 10+ files currently saved in a shared folder. Unfortunately, the schema changes across the files (new fields are added or removed month after month). Each file in the folder contain 2 worksheets (1) Vacancy (2) Pivot. I'm trying to combine all the files to create a master list. I came across this YouTube clip where #3 helped in my case where there was only 1 worksheet in the file (I tested one), but results in an error when there are multiple sheets that reads "Record #: Tool #: No sheet specified, you must specify a sheet." (https://youtu.be/MjGxzsMJINQ) I tried adding a comment, but it's an old clip and doesn't seem to be getting responses. Would anyone know how to distinguish the sheet to pull in using this solution? Thanks!!

[Steph23]

I've been upgraded to 2021.2 and @atcodedog05 your solution works like a dream! (Unfortunately, I'm still unable to open your file, @Qiu 😥) Thank you so much!!

[Qiu]

@Steph23 
Glad to know it works anyway. 😁

[atcodedog05]

Happy to help : ) @Steph23 

Cheers and have a nice day!

[Steph23]

@atcodedog05  - Is there a file count limit to the macro? There are 20+ files in the folder, but the macro only seems to be pulling in 10. Any work around for this?

[Steph23]

sorry... hit reply too soon and it doesn't seem like we can delete messages. I realized I'm filtering on one field that's impacting how many files come through. No issues. apologies again!