consolidation of excel sheets with vba (update)

[questionmark1]

STARTING POINT:
-in a sub-folder i do have different excel-files (each single file represents one business unit in a company) including
-several sheets (each sheet represents one cost center), which - regarding the format - all look the same,
-except: number of sheets per file and cell-values differ. the files/sheets contain income statements.
-income statements:
First Column (=y-axis): Description of Income/Expense position (e.g.: interest revenues, staff costs,...).
first row (=x-axis): timeline[2004, 2005, 2006,...]

Example for Table:

Cell(1,1) 2004 2005 2006
Revenues 10 12 13
Income x 8 8 9
Income y 2 4 4
Expenses 8 9 10
Staff costs 6 7 8
Administration costs 2 2 2
Profit 2 3 3


QUESTION:
How can I merge the figures of each of these tables into one file in the superordinated folder? I.e. all cells all cells in a defined range (e.g. B2:F200) have to be totalised in the according sheet.

Thank you in advance for your immediate help!

 

[SkipVought]

Hi,

First I'd recommend getting in familiar with

http://www.alphabetcitydataworks.com/FundamentalsOfRelationalDatabaseDesign.doc

Then come back and we can discuss the kinds of tables (sheets) that you need to build -- for instance ONE sheet for Cost Center with a NEW column for what's in each sheet tab and a NEW column for what's in each Workbook Name.

Skip,

_[red][/red]

 

[questionmark1]

Hi SkipVought,

Thank you for your answer. It seems that it focusses on SQL/Access/relational database.

What I am looking for is an excel-based solution with VBA-Modules, which gives me back the sum of all figures reported in the different files in a new consolidation file.

Or do I missunderstand your answer?

Regards, Q.

 

[SkipVought]

They ARE related. You do have a database of sorts.

For instance you have a separate workbook for each business unit and a sheet for each cost center. You cannot possible do data analysis or reporting across your enterprise with you data so segmented.

If related data (data that "all looks the same") were in a single table, it would be 1) easier to maintain, 2) easier to analyze and 3) easier to report on.

If your data were so organized, your question could be answeres in a matter of SECONDS (literally) using native Excel functionality WITHOUT VBA code.

Now you may need to develope some VBA to properly consolidate you data (after which you would DISCARD the workbooks you currently have)

Skip,

_[red][/red]

 

[questionmark1]

... now I see the link! My question does not cover the normal case! for the daily business we do have a well organised MIS, which allows to maintain, analyse and report our figures.

My question is related to a extraordinary problem, wich has to be solved in a short term perspective.

However, are you able to provide me with a adequate VBA-Code like "Get the sum of the figures reported in the sheets and files saved in a specific folder into one consolidation file."

Thank you!

 

[SkipVought]

Take a look at Data/Get External Data/Database Query -- Excel Files -- ONE OF YOUR WORKBOOKS -- ONE OF YOUR COST CENTER SHEETS.....

This is a way to access data in other Excel workbooks or other sheets.

There is a way to point the connection to various workbooks and sheets. The VBA that you would need is fairly simple. But try to query one of your tables and see what that result gets you.

Skip,

_[red][/red]