VBA help - sum across multiple workbooks

[maggie.hongmk]

I am using excel 2007.
I have several workbooks (and the NUMBER of workbooks could change), all with the same number of columns and structure showing the Income Statement of a particular business.
I would like to be able to have a SUMMARY workbook that will sum up all the workbooks that I choose to sum up in the same format. The data in each of the workbook is from column B to BI and I would like to, for example, sum all B15 numbers in the workbooks into cell B15 in the SUMMARY workbook; sum all B16 numbers in the workbooks into cell B16 in the SUMMARY workbook, and etc.

The range that I would like to sum is:
B15:BI24
B28:BI37
B41:BI50
B54:BI63
B67:BI76
B80:BI89

Is this possible? I am new to the VBA world.

Thanks

 

[Andrew Poulsom]

Welcome to MrExcel.

Do you mean worksheet rather than workbook?

 

[maggie.hongmk]

No, I meant workbook.

I have a general model/template that I use to get a general sense of the profitability of a business and there is a tab labeled "P&L_ByProduct".
Each business will have its own workbook with a P&L_ByProduct tab.
I want to roll up this tab into a summary to see overall profitability of whatever business/workbook I select to consolidate.

 

[Andrew Poulsom]

This may help you:

Create a summary worksheet from different workbooks (with formulas)

 

[maggie.hongmk]

Thank You for the quick response Andrew.

In that example, it only allows me to copy over one line from each workbook into the SummarySheet (I used example 2 in the example you provided me).
I tried modifying it so that in my "summary" workbook, I have multiple worksheets. Worksheet 1 would consolidate the Revenue, worksheet 2 would consolidate the costs. But when I duplicate the code (one for worksheet 1, one for worksheet 2) it still only imports the data all into worksheet 1. How would I go about modifying the code so that the data isn't imported into worksheet 1, but worksheet 2?