Excel VBA Sum value cell from multiple files in to one file

Topic

New Reply

I am trying to create an excel macro to take values from a set of Excel files called source and aggregate them in a file called destination. Each set of source files have the same format, in depending on the name, each file having several sheets.
Source files that start with the same number belong to the same set.

For example, with the following two sets of files:
2_A_source.xls; 2_B_source.xls; 2_C_source.xls;
3_A_source.xls; 3_B_source.xls; 3_C_source.xls; 3_D_source.xls
this macro to create two files destination, called 2_destination.xls
and 3_destination.xls, each of them cells to sum values in the set.
I attached the source file format for clarification.

Can you help me?

Reply | Quote

Replies

Hello
Have you considered the built in ability for excel to consolidate data from multiple sources.

This is found on the ribbon int the Data Tab, Data tools group, consolidate button.

If the sheets are the same format and structure it ise possible to build formulae like this

=sum(sheet1:sheet5!D3)
This sums all the values in D3 for the sheets sheet1 to sheet5

You may not need a macro.

Regards
Geof

Reply | Quote

Hi,
As you can see in the example file, each workbook has several sheets. Each sheet is different in format.
Destination workbook file consists of several sheets.
Each sheet from this workbook destination is a sum of a every sheet coresponding from the source workbooks .

I need the destination workbook be sent by e-mail without workbooks source.
It is possible that using the consolidated button?

Best regards!

Reply | Quote

Hi
Yes you can perform the consolidation without links to the source. This can then be emailed.

I am wondering now about whether this will suit your needs. How many files and worksheets are you using?

G

Reply | Quote

Hi,
I use a minimum of 12 source files for each set (5 sets), each file is 4 worksheets.
There will be 5 files destination (one for each set), each file is 4 worksheets.
Both files source and destination have the format that I attached in the first post.
Thank you for your help.

D

Reply | Quote

Hi
Could the following steps work for you. It is a process you have to make clear for a programmer anyway.

[*]Consolidate each of the 5 sets of 12 into single workbooks of 4 worksheets. Each of these 4 worksheets would be the result of consolidating 12 worksheets.

This results in 5 workbooks of 4 worksheets each

[*]Consolidate these 5 workbooks into a new workbook of 4 worksheets.

This could be tricky to maintain with lots of external references.

When you do the consolidation there is an option to specify whether you want to preserve links to the source.

Regards
Geof

Reply | Quote

Hi,
Unfortunately, it can not do consolidation these 5 workbooks into a new workbook of 4 worksheets because data entry are different and have different reports.
Thank you for your help.

Reply | Quote

Hello again
Unfortunately I cannot help much further.

The lines of code below show two methods of summing a range under VBA. It is only a very simple example. The data needs to be integer.
The example requires a worksheet named “North” that contains data in a range of B2:D4.
If you experiment by uncommenting lines 4,5 and 6 you can see the effects of each approach.

Code:

Sub ReturnTotal()
Dim iTotal As Integer
'--- approach 1 ------
'Dim North As Worksheet
'Set North = ActiveSheet
'iTotal = Excel.WorksheetFunction.Sum(North.Range("B2:D4"))
'-----
'==== approach 2 ===
iTotal = Application.Sum(Range(Cells(2, 2), Cells(4, 4)))
'===
MsgBox (iTotal)

End Sub

Regards
Geof

Reply | Quote

I understood that I was not well, so back with explanation.
Here’s an example of what I get:
I have two workbooks called 2_A_source.xls and 2_B_source.xls.
Each of these two workbooks has 4 different worksheets together, attached file after model in the first post.

Creating one called 2_destination.xls workbooks, worksheets which has 4 different between them, each of them identical format as the source file Sheets. In this workbook will have to sum up data from the first two files as follows:
In cell B15 of worksheet 1 must sum up the values ​​from the two source files (2_A_source.xls.Sheet1.B15 + 2_B_source.xls.Sheet1.B15).
In cell C15 of worksheet 1 must sum up the values ​​from the two source files (2_A_source.xls.Sheet1.C15 + 2_B_source.xls.Sheet1.C15).

And so on, until complete all range (B15: I15).

In worksheet 2 of 2_destination.xls workbook, it works like the model above, but the values ​​that will sum up the range (C14: F24).

In worksheet 3 of 2_destination.xls workbook, it works like the model above, but the values ​​that will sum up the range (C14: F21).

In worksheet 4 of 2_destination.xls workbook, it works like the model above, but the values ​​that will sum up the range (C14: F25).

I explained only one set of source workbooks. If you have 5 sets, I think a lot would ease macro work.

Thanks in advance if you can help me.

Reply | Quote

Hello again
Thank you for your explanation.

I have attached a sample destination workbook.
Does it look the way you like it?

41420-Doru-destination

Geof

Reply | Quote

Hello!
It’s just what I needed.
I will adapt to all sheets and all sets.
Thank you very much.

With great respect,
Doru

Reply | Quote

Hello
That is excellent.
I created this using only the consolidate tool on the data tab, data tools group.
Each of the 4 worksheets will have references to the supporting workbooks and worksheets. An example is shown in the screen shot below.

41429-DoruConsolidateScreen

You will find it easier if you have all 12 workbooks open as you build the destination workbook.

In the consolidate dialog you can then click in the ‘Reference’ panel and then navigate to the desired workbook and select the needed range of cells. Once the Reference panel is showing the right information Click on the ‘Add’ button to fill the panel named ‘All References’.

Repeat the process above for each of the 4 worksheets in the destination workbook.

To build a macro for this could be a significant task. The problem is that you will duplicate the existing functionality that Microsoft have given us.
In my opinion just recording the process results in a list of specific files, paths and filenames. It will be harder to maintain such a macro than it is to maintain the workbook with the ‘Consolidate’ dialogue box.

There is a snippet of a macro recording named ‘macro 9’ in the sample I sent. This type of thing would be a nightmare to maintain. It would be easier to just record again.

Regards
Geof

Reply | Quote