Combining Sheets from 5 Workbooks into 1 Sheet

Hello,

I am trying to combine data from 5 different workbooks into one sheet. They all have the same column headers and the same types of data in each column. For simplicity sake, assume that the workbooks are called Book1, Book2, Book3, etc. and are located on the desktop. I am really running into issues when it is copying the data over and having it automatically detect the last row of data EACH time so it knows where to paste the new data. I have attached 3 workbooks with simple data, so you can see exactly what i'm trying to do. Any help would be much appreciated. Thank you!

So I went in and tried to use what you had posted by only changing what you told me to change. For some reason, the debugger comes on and tells me that I cannot have Loop with Do. See code:

Please Login or Register  to view this content.

I see a Do, so I do not know why that is happening. Also, I put this in for fName. Is it correct? Thanks again.

Please Login or Register  to view this content.

Sorry to keep bothering you, but now I am getting a "subscript out of range" error. I changed the name of the file to pull in .xlsx because I am using 2007. Below is my code. Any thoughts? Thanks again.

Please Login or Register  to view this content.

When I put the cursor over the line:

Please Login or Register  to view this content.

it tells me that name="". The error message reads: Run Time Error 91: Object Variable or With block variable not set". Thanks for your help.