Combine multiple sheets which have multiple tables on each sheet - Macro/VBA?

[questforexcel]

Hi All,

I am not familiar with Macro/VBA.

So I have a situation - where i have 20 files that I need to combine.

Each of these 20 files have about 6 tabs each.

I will list these 6 tabs out as follows:

1) Staff and Part Time Resources
2) S&D Expenses
3) Overhead Expenses
4) Prior Year Staff and Part Time Resources
5) Prior Year S&D Expenses
6) Prior Year Overhead Expenses

Some of my tabs like S&D Expenses, Overhead Expenses will have multiple sub expenses on that sheet. My objectives are as follows:

1) Consolidate the 20 files in a way that main master file will have 6 tabs. (This i understand should be possible)

2) I would like the consolidation to take place by the sub expenses section on each sheet

Is it possible to have the macro add and consolidate data in each sheet by each sub expense type as shown below. So in below, I would need the macro to consolidate all the Marketing Expenses together and then move on to the Direct Selling Costs.

Thanks,

 

[EFANYoutube]

This is not a straight forward task, is this a one time thing or is it a repeated task, does the data differ greatly when repeated?
There are a lot of things that need to be considered, yes a code can be made to do what you ask but it would take some time and would be vulnerable as the data is not guaranteed to be in the exact same format each time.
If it is a one time thing, it would be better to do this semi-automated
If it is a recurring task, it would be better to do this in code but as it would rely on very static parameters it would be fragile and could break easily if the data changed slightly

Hope this will help you consider your question a bit more