Consolidate data from multiple sheets into 1

High level, here's the problem. I have a workbook with multiple cost centers. The rows are accounts and the columns are the time periods. Users enter data for each cost center by account and month.

I need to take the detail from each tab (cost center) and consolidate it into 1 list. I need to do this to import to database. The database will accept cost center and account as the first 2 columns with the data as the remaining columns.

What I want to do is is create something that will take the account by time period from each tab and paste it into 1 consolidated sheet. The first problem is that I need the tab name (i.e. cost center number) to populate Column A (for each of the rows) in the consolidated tab. Another problem is that the number of rows can vary on each sheet.

Any ideas?? :thanx:

I have attached a sample workbook that has dummy data and the result that I am looking for. Please help!!

Re: Consolidate data from multiple sheets into 1

Thank you for the suggestion. I would gladly delete the columns but I can't. This template is sent out to CC mgrs and format unfortunately is important. I can get rid of the blank rows without a problem but am still looking for a macro solution that will copy the relevant data over and assign the tab name for the range into column A.

Any thoughts folks?

Re: Consolidate data from multiple sheets into 1

Unfortunately not. The database does require this layout and with the number of cost centers to import, create a pivot table may not even work...we've run into limits with the data size before on PTs.

Thanks for trying!

Re: Consolidate data from multiple sheets into 1

I am SO sorry! I opened the wrong file before. This works perfectly!!! You are a god send. Thanks so much for your efforts!