VBA to merge and tally table data

[Henrybukowski]

Dear esteemed posters,

Simply put, I need a macro - I'm aware that there are many macro's out there which combine data from worksheets - but I have trouble making them fit my needs.

All I want to do is combine identical data in identical structure from different identical workbooks, into an existing excel document(presumably where the document is placed) in the same format.
I feel that the best help I can give to anyone kind enough to offer advice, is to post this example sheet along with screen shot:

Imagine, as in the attached file, that I have a number of workbooks in which there is the above table in the same place in worksheet 1 of each. All I need to do is add all these tables together into an existing document so that the table stays the same but the totals are all added together. (jury is out as to whether this is overly adventurous) In other words so that I have a summary tally of relationship statuses (a fictional example)

What I'm trying to avoid is the situation where each table is appended next to each other. And a really big emphasis for me is that the macro does this into an existing sheet, so I can pre-programme that sheet with formulas if needs be.

Any help would be hugely appreciated.

Many thanks in advance.

Henry

 

[sijpie]

First of all, if you post in two forums, please mention that with a link on both, so that no unnecessary work is being done. This is one of the rules of both forums in this case!

At Ozgrid: Macro merging and tallying table data


I can't see the image here, because Ozgrid will not show it to a link, but not to worry.

Just to repeat in my own words what you want to achieve:

1. You have a (unspecified) number of workbooks with identical lay-out on a certain sheet
2. You want to update the sheet in the 'Summary'workbook so that each field is the sum of the values in all the other workbook

OK, this is not very difficult to achieve. I would create an array of the range to be summed (say A1:Z50). Then loop through each workbook and load the range into a similar array, then add the values in each cell to the master array.

then write back the array to the summary workbook.

For this it will be helpful if all the workbooks are in one location and are the only WB in that folder, or can be identified by a certain name or a certain characteristic (like a specific sheet name). This is to make it easy to loop through the WB and only process those that have that characteristic.

Also since I don't have an image, let me know the range. (or repost the image: store the image in Google+ or so, make it public and then link to it)