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

    Firstly I will suggest, organize your data in a bit better manner.

    Your cost center sheets has too much data that is not required....
    like quarterly results, subtotals... etc.

    You can still take this and write a code to delete the unwanted data (rows / columns) at one go.

    Next... you can use the pivot table feature of "multiple consolidation ranges".

    I tried it and almost got what is required... you will need to copy the pivot table paste as value and do the additional formatting... which should not take much time.

    See the file attached...

  • 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?

    :thanx: Tony

  • 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!

    :thanx: Tony

  • 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!

    :thanx: Tony

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!