Consolidate Divisional Sheets to Corporate Summary

[jaslake]

I'm attempting to consolidate Divisional Sheets into a Corporate Summary. I've searched the net for a week on this subject and have not found a working solution. The following code is a compilation of code I've found

Option Explicit
Public Sub ImportData()
    Dim shArray() As Variant
    Dim sh() As Variant
    Dim x() As Variant
    Dim n As Integer
    Dim i As Integer

    Application.ScreenUpdating = False
    n = Sheets.Count
    ReDim sh(n), shArray(n)
    For i = 1 To Sheets.Count
        sh(i) = Sheets(i).Name
        Debug.Print sh(i)
    Next

    For i = 1 To n - 1
        sh(i) = Worksheets(i + 1).Name
        shArray(i) = Array("'" & sh(i) & "'!R1C1:R70C13")
    Next i
    x = shArray

    Worksheets("Consolidated").Range("A1").Consolidate _
            Sources:=x, _
            Function:=xlSum
    Application.ScreenUpdating = True
End Sub

I get a "Type Mismatch" error at this line of code

Worksheets("Consolidated").Range("A1").Consolidate _
            Sources:=x, _
            Function:=xlSum

I can do this other ways but this is part of a much larger monthly consolidation process. I'd appreciate it if you could look at the attached workbook and give me some direction.
J

[Rick_Stanich]

It appears that the variable "shArray(i)" is null. Thus your type mismatch error.

FYI, adding "MsgBox shArray(i)" after "x = shArray" displays an empty message box.

[royUK]

The Summary sheet example shows how t do this without VBA

http://excel-it.com/workbook_downloads.htm

[jaslake]

This is an interesting approach http://excel-it.com/workbook_downloads.htm and I'm certain I'll use it for some applications. What I'm interested in is creating an array of sheets to be "consolidated" with the "Consolidate" function in VBA. Any idea how to make this work?
J

[royUK]

When I try using Consolidate I get an error message saying that Excel cannot open the sheets. I haven't found any working examples of Consolidate and I cannot see how it is different to the method on my website, which I would think is more flexible in that you can move sheets out of the consolidation for comparisons/what ifs.

[jaslake]

I've spent days on this topic and I too have been unable to find any "working" solutions using the "consolidate" function in VBA. I'll spend some time with your approach, importing files from different workbooks and see what i can put together.
Thanks for your input. J

[royUK]

Post back if you want further help