How to merge multiple excel files..?

Hi all, please excuse my new-bee-ness..I have searched far and wide and can not get an answer...

Situation: I have about 80 excel files I have to input into a database for access.
Problem: Instead of loading one file at a time (Access import external data allows only one at a time) I wish to upload one file with all the 80 files in one.
My proposed solution: I was thinking, open each excel workbook, and then move the tab from each of the 80 workbooks into one main workbook (that i will use, ie like a hub)..Then once the main workbook is complete with the 80tabs, I will find a way to move the data from the tabs into one single tab to upload!

Please assist if you know a smarter, faster way to do this...I have do not want to do this tedious work

PS: All the files have the same headers, and the tab2 is the one i want, which they all open to tab 2 when you open the files.

This is my stock macro for this task. You'll need to edit the sections in red so that it functions the way you want.

Please Login or Register  to view this content.

Just name your "Master" sheet for the import and put in the ROW1 headers. The macro will do the rest.

Thanks - did everything but getting

Runtime Error 9 on the below text (in yellow);

Please Login or Register  to view this content.

let me know the specific steps if I am missing something..

Essentially,
I created a master file and put it into a folder called "Done".
Then i changed the paths and dest paths, and ran the macro but got the error above...

I can see this almost working, what am i doing wrong tho

columns in use is A to O, but the data could be as long as line 1,000 (row).