appending multiple workbooks with same headers

[bibu]

I literally have 100 workbooks with the same header, is there any way to just put everything together in one .xls file? *except copy/paste
I got the idea of building a macro that copies the content but pasting it is a different matter, I can't get the macro to paste the content on the next blank cell everytime I need to add content.
I'll attach two sheets for exemplification.
TY in advance.

[rcm]

Hi, this code will do the job, yet, you need to adjust the proper directory for the files and create the "consolidated.xlsx" and list all the desired filenames in sheet2

Please Login or Register  to view this content.

[bibu]

Thank you for the reply. I don't fully understand the code, but I think I have made a confusion, I'm sorry.
As I said before, there are 100 worksheet (different .xls files) that have the same header and the same structure (only the first sheet "OnCourt" contains data) and I would like to move all their data in a single ("consolidated" as you say) sheet.
Now, considering that all the files (both the "consolidated.xls" and the other files) are in the same folder, I'll show you what happens when I try to run your code.
- Note that I'm modifying "On Court" to "OnCourt" since that's the name of the sheet.
I think I'm doing something wrong or I didn't explain myself correctly in the first post.
https://dl.dropboxusercontent.com/u/...306_111944.mp4

Sorry if file takes too long to load, it has 20 MB.
Basically I get
"Compile error
Function call on left-hand side of assignment must return Variant or Object"

[bibu]

Maybe this should be moved to VBA/Macro, since I've seen similar threads in that category.