Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Jun 2015
    Posts
    13
    Thanks
    11
    Thanked 0 Times in 0 Posts

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

    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?
    Attached Files Attached Files

  2. #2
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts
    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

  3. The Following User Says Thank You to geofrichardson For This Useful Post:

    doru.ditu.3 (2015-07-22)

  4. #3
    New Lounger
    Join Date
    Jun 2015
    Posts
    13
    Thanks
    11
    Thanked 0 Times in 0 Posts
    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!
    Last edited by doru.ditu.3; 2015-07-21 at 15:37.

  5. #4
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts
    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

  6. The Following User Says Thank You to geofrichardson For This Useful Post:

    doru.ditu.3 (2015-07-22)

  7. #5
    New Lounger
    Join Date
    Jun 2015
    Posts
    13
    Thanks
    11
    Thanked 0 Times in 0 Posts
    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
    Last edited by doru.ditu.3; 2015-07-22 at 09:38.

  8. #6
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    Consolidations

    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

  9. The Following User Says Thank You to geofrichardson For This Useful Post:

    doru.ditu.3 (2015-07-23)

  10. #7
    New Lounger
    Join Date
    Jun 2015
    Posts
    13
    Thanks
    11
    Thanked 0 Times in 0 Posts
    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.
    Last edited by doru.ditu.3; 2015-07-23 at 11:40.

  11. #8
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    Creating a sum with VBA

    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 B24.
    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

  12. The Following User Says Thank You to geofrichardson For This Useful Post:

    doru.ditu.3 (2015-07-24)

  13. #9
    New Lounger
    Join Date
    Jun 2015
    Posts
    13
    Thanks
    11
    Thanked 0 Times in 0 Posts
    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.
    Last edited by doru.ditu.3; 2015-07-24 at 02:40.

  14. #10
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts
    Hello again
    Thank you for your explanation.

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

    Doru-destination.xlsm


    Geof

  15. The Following User Says Thank You to geofrichardson For This Useful Post:

    doru.ditu.3 (2015-07-24)

  16. #11
    New Lounger
    Join Date
    Jun 2015
    Posts
    13
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Hello!
    It's just what I needed.
    I will adapt to all sheets and all sets.
    Thank you very much.

    With great respect,
    Doru

  17. #12
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    Building the consolidation

    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.

    DoruConsolidateScreen.png

    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

  18. The Following User Says Thank You to geofrichardson For This Useful Post:

    doru.ditu.3 (2015-07-25)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •