Using VBA to Perform Consolidate Function

[mistera]

I am trying to build the source array in the consolidate function using VBA and then execute that function. However, I'm getting a 1004 runtime error ("Consolidate method of Range class failed."). Can someone please tell me what I'm doing wrong?

I have a named range in each worksheet for the same range of cells that I'm trying to consolidate into the worksheet called "Total". The named ranges are all named "Total_xxx", where xxx is the name of the worksheet. The only exception is the named range on the "Total" sheet and if there are any sheets with a "-", the named range replaces the "-" with "_".

When I open the workbook, I'm building a public global variable called strConsolidate to be the source for the consolidate statement.

Here is the code in the open workbook event:

Private Sub Workbook_Open()
' Purpose: Prepare statement for running the consolidate command.
    Dim ws As Worksheet, booFlag As Boolean
    booFlag = True
    strConsolidate = "Array("
 
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Total" Then
            If booFlag Then
                strConsolidate = strConsolidate & """Total_" & ws.Name & """"
                booFlag = False
            Else
                strConsolidate = strConsolidate & ", ""Total_" & ws.Name & """"
            End If
        End If
    Next
 
    strConsolidate = strConsolidate & ")"
    strConsolidate = Replace(strConsolidate, "-", "_")
 
End Sub

When I query the strConsolidate string in the immediate window after this routine, it looks right -- like this:

Array("Total_30001", "Total_30002", "Total_30003", "Total_30004", "Total_30005", "Total_30006", "Total_30007", "Total_30008", "Total_30009", "Total_30010", "Total_32004", "Total_32008", "Total_88002", "Total_88014", "Total_88022", "Total_88022_Init", "Total_88031", "Total_88047", "Total_88048", "Total_88051", "Total_88061")

Here is the code to execute the consolidate function, where it bombs out:

Public strConsolidate As String
Sub ConsolidateTotals()
' Purpose: Consolidate data on Total Sheet.
    Sheets("Total").Select
    Range("C2").Select
    Selection.Consolidate Sources:=strConsolidate, _
        Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False
 
End Sub

 

[Jerry Sullivan]

Hi mistera,

The "Sources:=" Parameter of the Consolidate.Method needs to be an actual Array instead of a String.

Try...

Sub Consolidate_Totals()
    Dim ws As Worksheet
    Dim sArray As Variant, i As Integer
    ReDim sArray(1 To 1)
 
    '---Make Array with Named Ranges to be Consolidated
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Total" Then
            i = i + 1
            ReDim Preserve sArray(1 To i)
            sArray(i) = Replace("Total_" & ws.Name, "-", "_")
        End If
    Next ws
    If i = 0 Then Exit Sub
    
    '---Consolidate using the Array
    Sheets("Total").Range("C2").Consolidate Sources:=(sArray), _
        Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False
End Sub

This example Sub doesn't use the Workbook_Open Event or any Public Variables.

Is there any reason to use the Workbook_Open instead of just building the list of Ranges when right before the Consolidation?

 

[mistera]

Jerry, you're awesome! That worked like a charm! The reason I was building the list of ranges on opening the workbook is that I had planned to execute the consolidate function every time a change is made to the workbook and I didn't want to have to perform the build with every change.

However, after seeing your resolution, I think I will just execute the consolidate function when the Total sheet (where the consolidation occurs) is selected and before saving the workbook (in case the user never selects the Total sheet).

Thanks for your help!

 

[NPSmalley85]

OK I have a sheet with a list of filepaths '\\dfz****\Folders\DOFR\B&NS\UC\Shared\1. Working Version\[BOLTON.xls]Forecast Template'!$F$5:$AT$1230.
I've used the following code to consolidate the data from all the Forecast Templates:

Sub Forecast_Consolidate()
Dim ws As Worksheet
Dim sArray As Variant, i As Integer
ReDim sArray(1 To 1)
Dim lngLastRow As Long
lngLastRow = Cells(Rows.Count, "D").End(xlUp).Row
Application.ScreenUpdating = False
'---Make Array paths in Formula
For Each cell In ThisWorkbook.Worksheets("Formula").Range("D2:D" & lngLastRow)
i = i + 1
ReDim Preserve sArray(1 To i)
sArray(i) = (cell.Value)
Next cell
If i = 0 Then Exit Sub
'---Consolidate using the Array
ThisWorkbook.Sheets("Forecast Template").Range("F5").Consolidate Sources:=sArray, Function:=xlSum
End Sub
However, when it runs it says 'Cannot open consolidation source '[Bolton.xls]Forecast Template', then I get runtime error 1004.

Help!

 

[Doug Glancy]

Hi Jerry,

Nice answer. I've never used Consolidate, but came across this answer and modified it to answer a question on Stack Overflo: Create Excel Consolidated Worksheet with multiple sources in VBA - Stack Overflow

I'm curious whether you've ever used Consolidate in actual practice. It seems rather static to be useful.

 

[Doug Glancy]

Wow, If I type Stack Overflow the second word is asterisked out! But not this time.

 

[Jerry Sullivan]

Hi Jerry,

Nice answer. I've never used Consolidate, but came across this answer and modified it to answer a question on Stack Overflo: Create Excel Consolidated Worksheet with multiple sources in VBA - Stack Overflow

I'm curious whether you've ever used Consolidate in actual practice. It seems rather static to be useful.

Hi Doug, No I haven't used Consolidate in practice. The little bit that I've worked with it, it seems rather cumbersome and limited in comparison to VBA or SQL.

Cheers!