VBA CODE: consolidation of excel sheets

As a very VBA-beginner the tasks I am confronted with are more challanging than my progress ;-). So I urgently need your pleasant help!

STARTING POINT:
-in a sub-folder (c:\company_xy\Business_Units\) 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)
-these sheets - regarding the format - all look the same,
-except: number of sheets per file (=number of cost centers) differs
-these files/sheets contain income statements as follows:
First Column (=y-axis): Description of Income/Expense position (e.g.: interest revenues, staff costs,...).
first row (=x-axis): timeline[2004, 2005, 2006,...]

(please see the attached example)

QUESTION:
How can I merge the figures of each of these tables into one file (i.e. company_income_statement.xls) in the superordinated folder (c:\company_xy\company_income_statement.xls)? I.e. all cells in the defined range (i.e. B2:D8) have to be totalised in the according sheet.

Can somebody of you guys help me by providing me the according VBA-code? Thank you in advance and best regards, Q’mark

Hi Questionmark,

Ron de Bruin has an extensive range of sample code at:

http://www.rondebruin.nl/tips.htm

See, for example:

http://www.rondebruin.nl/copy2.htm

and

http://www.rondebruin.nl/copy3.htm


---
Regards,
Norman



"questionmark" <[email protected]>
wrote in message
news:[email protected]...
>
> As a very VBA-beginner the tasks I am confronted with are more
> challanging than my progress ;-). So I urgently need your pleasant
> help!
>
> STARTING POINT:
> -in a sub-folder (c:\company_xy\Business_Units\) 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)
> -these sheets - regarding the format - all look the same,
> -except: number of sheets per file (=number of cost centers) differs
> -these files/sheets contain income statements as follows:
> First Column (=y-axis): Description of Income/Expense position (e.g.:
> interest revenues, staff costs,...).
> first row (=x-axis): timeline[2004, 2005, 2006,...]
>
> -(please see the attached example) -
>
> QUESTION:
> How can I merge the figures of each of these tables into one file (i.e.
> company_income_statement.xls) in the superordinated folder
> (c:\company_xy\company_income_statement.xls)? I.e. all cells in the
> defined range (i.e. B2:D8) have to be totalised in the according
> sheet.
>
> Can somebody of you guys help me by providing me the according
> VBA-code? Thank you in advance and best regards, Q'mark
>
>
> +-------------------------------------------------------------------+
> |Filename: Example_BU.doc |
> |Download: http://www.excelforum.com/attachment.php?postid=4986 |
> +-------------------------------------------------------------------+
>
> --
> questionmark
> ------------------------------------------------------------------------
> questionmark's Profile:
> http://www.excelforum.com/member.php...o&userid=36096
> View this thread: http://www.excelforum.com/showthread...hreadid=558764
>

Dear Norman,
Thank you for your immediate answer. The link sent by you looks very interesting and seems to be helpful. Let me check this! Kind regards, Q'mark

Dear Norman,
Thank you for your immediate answer. The link sent by you looks very interesting and seems to be helpful. Let me check this! Kind regards, Q'mark