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

jayped

Board Regular
Joined
Mar 20, 2019
Messages
54
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!
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

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
Back
Top