Consolidate Divisional Sheets to Corporate Summary

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

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.

The Summary sheet example shows how t do this without VBA

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

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

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.

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

Post back if you want further help