Combine multiple sheets from multiple books into one

Hi all,

What I am trying to do it quite simple (I hope!)

I have 6 spreadsheets all within the same folder, these are pretty much identical (rows, colums, sheets within them) apart from the names of the files.

I then have a master spreadsheet within the same folder where I want to combine all the data, from all the sheets within each book (if that makes sense!) apart from the data on the last sheet within each book as this is the reference data, onto one sheet within this master file. If possible I only want to copy rows accross which have complete data too.

So: (names not correct)
From book1.xls copy all data on sheets (sheet1, sheet2 etc) except last sheet
From book2.xls copy all data on sheets (sheet1, sheet2 etc) except last sheet
combine onto masterfile.xls on sheet1.

I have searched on here and can only find how to do it with the first sheet in each workbook, not looping through all the sheets in each book. Please see below.

Many thanks in advance.

Please Login or Register  to view this content.

I think this does what you want

Please Login or Register  to view this content.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save