Let us demonstrate, using a business case of constructing a summary table of the expenses of the company LLC “Alliance” for the reporting period, how to create and delete consolidating tables in code. For this, create a workbook containing several sheets, for example, January, February, March, with tables In addition, the workbook must contain an empty sheet named Summary. After that, add the corresponding code to a standard module and to the ThisWorkbook module.
NOTE
The program consolidates an unspecified number of tables. Therefore, you cannot use the Array function (with an unknown size) as the value of the Sources parameter of the Consolidate method. This issue is easily solved in the program — by introducing an additional variable of type Variant, assigning it the values of a dynamic array containing the addresses of the consolidated tables. Afterwards, this auxiliary variable is used as the value of the Sources parameter.
The standard module contains two procedures that implement the business logic of the project:
- ConsolidationBuilder: builds the consolidating table from any number of data sheets whose names differ from the sheet name of the consolidating table (i.e., from the sheet Summary). Before creating the required constructions, this procedure checks whether the Summary sheet already contains a table (more precisely, whether there is any data in its first column). If such data is present, no new construction is performed.
- ConsolidationKiller: deletes the consolidating table. More precisely, it removes the structure created by this table using the ClearOutline method and clears the cell contents using the Clear method. Before deletion, this procedure checks for the presence of such a structure on the worksheet, and if it does not exist, deletion is canceled as unnecessary.
In the ThisWorkbook module, there are two procedures that place the necessary buttons on the Add-ins tab of the ribbon when the workbook is opened, and remove them when the workbook is closed :
- The Open event procedure of the Workbook object constructs a toolbar Consolidation (classic style), on which two buttons are created: Consolidate and Delete Consolidation. These buttons appear on the Add-ins tab in the Custom Toolbars group and execute the procedures ConsolidationBuilder and ConsolidationKiller.
- The BeforeClose event procedure of the Workbook object removes the created Consolidation toolbar with its buttons when the workbook is closed (and, accordingly, the Add-ins tab will not appear when other workbooks are opened).