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!
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!
