Consolidate multiple workbooks with multiple sheets into a Masterfile; maintaining sheet names and Summation of values

jayped

Board Regular
Joined
Mar 20, 2019
Messages
52
Hi.

I have multiple workbooks with multiple sheets. The workbooks each have the same layout and sheet names. What's different would be the values in each tab. I have a master workbook, same number of sheets with sheet names, etc. which I would like to be updated with the *summation* of the values in the other workbooks, in other words a consolidated workbook. Most VBA's I've come across copy and paste and combine everything into one sheet, But I need to maintain the sheet names in the consolidated master file. In each sheet in each workbook the "items" are listed vertically in column C, and columns F & G carry the values. So I'm wondering if a VBA can be created to look up the "item" in column C and return a sum value for that particular item in each workbook to the masterfile in the respective sheet.

Other things to bear in mind:
- there is a particular tab that I do not want to sum but copy and paste each cell with data into the masterfile in the same sheet. Column headings are B to J in row 23.
- there are formulas in the master file that sum certain rows on a particular sheet. Its either the macro replaces the formula and performs a sum itself based on the item in column C or it disregards cells with formulas altogether. I'm thinking the latter could save running time. NB: The cells that require updating all contain '0'.
- If I want to exclude a particular sheet from each workbook can this also be included?

Additional info:
In each sheet the headings in columns F and G are on row 6, but the first "item" to look up is on row 10. There are headings in column C but there are sporadic.

Appreciate any help I can get on this!
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Watch MrExcel Video

Forum statistics

Threads
1,127,505
Messages
5,625,199
Members
416,080
Latest member
blemon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top