Consolidate multiple workbooks and worksheets from a folder

[sharc316]

Hi,

I've been searching literally all day for a solution to this and cannot find anything that works. Everything I've tried gives me errors. Please see below:

I have multiple excel/.xls files in a folder and each workbook has multiple sheets. Each worksheet has consistent data structure across each of the files, however each file might not have the same number of worksheets. Meaning that if there is no data for a particular category is a certain period then the worksheet will not be created. I would like to consolidate all the data into one file.
- The master file should have the first row as data labels in each of the work sheets
- The worksheets should be matched by name, if possible, and consolidated
- The row range is dynamic and cannot be defined. Need some sort of procedure to check if there is no more data

Any help with this would be greatly appreciated.

[apo]

Can you upload a zip file that contains your master workbook and a few of the other 'data' workbooks.. gives us helpers something to work with and test...

[sharc316]

I've attached the two sample data files.

For the master file, is it possible to enter a destination path for it? Also, would it be possible to append/add data from future statements to the master file one by one as they become available?

Thank you very much for your help.

[jdigennaro]

I have been looking for a similar macro. I have multiple workbooks each containing exactly one sheet. All sheets in all workbooks are the same format. I just want a macro that opens the Excel file, copies range B9:J54 to the master, closes the file and goes to the next file to repeat by posting to the next available row. The files are password protected but if that is an issue the password can be turned off. My VBA skills are minimal and I have tried some macros I found on the Internet, but so far nothing has worked well.

[apo]

HI sharc316..

Is "sheet1" in both both workbooks a representation of what you want the 'Master' workbook to look like?

[sharc316]

Hi Apo - sheet 1 is the raw data. All tabs following pull data from sheet 1. So sheet 1 does not have to be consolidated, only the tabs following it (all other tabs in the workbooks.)

I've attached a file that can be used as a master. Would like to leave sheet 1 in this file as is and place consolidated data in new tabs.