VBA help - sum across multiple workbooks

maggie.hongmk

New Member
Joined
Nov 28, 2012
Messages
23
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
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

maggie.hongmk

New Member
Joined
Nov 28, 2012
Messages
23
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.
 

maggie.hongmk

New Member
Joined
Nov 28, 2012
Messages
23
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,295
Messages
5,600,789
Members
414,405
Latest member
Zaurb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top