Consolidation Using 3D Formulas on a Worksheet with Excel VBA

As an example of consolidation using 3D formulas, let’s consider a business case of building a consolidated table of expenses for the company Alliance LLC for the reporting period from January to March. These expenses are collected in tables located on the worksheets January, February, and March. The company’s expenses are detailed quarterly.

So:

• Create a worksheet called Totals, where you will place the template of the report table.
• Enter in cell B3 the formula that calculates the total expenses for telephone in the first quarter from January through March:

=SUM(January:March!B3)

or the equivalent formula:

=SUM(January!B3,February!B3,March!B3)

• Place the mouse pointer on the fill handle and drag it down and to the right over the range B3:E8. This will allow you to calculate the total expenses for each category of expenses from June through August.

Consolidation Using 3D Formulas in Code

The procedure for creating a consolidated table based on 3D formulas, described in the previous section, can be automated with the following code.

The code includes a check for the existence of a worksheet named Totals. If such a sheet does not exist, it is created; if it already exists, a message is displayed and the process of building the consolidated table is interrupted.

Consolidation Using 3D Formulas

Sub DemoConsolidate3D()
    Dim rgn As Range
    Dim ws As Worksheet
    Dim str As String
    Dim nm As String   
    nm = "Totals"   
    For Each ws In Worksheets
        str = str & ws.Name & "!B3" & ";"
        If ws.Name = nm Then
            MsgBox "The Totals sheet already exists"
            Exit Sub
        End If
    Next   
    str = Left(str, Len(str) - 1)   
    Worksheets.Add After:=Worksheets(Worksheets.Count)
    ActiveSheet.Name = nm
    Worksheets("January").Range("A1:E8").Copy Worksheets(nm).Range("A1:E8")  
    Range("B3:E8").Clear
    Range("B3").FormulaLocal = "=SUM(" & str & ")"
    Range("B3").AutoFill Destination:=Range("B3:B8"), Type:=xlFillDefault
    Range("B3:B8").AutoFill Destination:=Range("B3:E8"), Type:=xlFillDefault
End Sub