I am writing a series of macros that will sum details from multiple
worksheets onto a summary page. It seems to work find until it gets to a summary which includes 10 detail pages (lots more than the ones that work). I then get the error 1004 - Unable to set the FormulaArray property of the Range Class. When I cut and past the formula from the debugger into the spreadsheet, for the area selected by the vba macro, it works fine. How do I get it to work from the macro?

Thanks in advance,
Rosanne

p.s. the formula looks like this:
: strFormula : "= + '1.1.4.1'!W13:AH32 + '1.1.4.2'!W13:AH32 + '1.1.4.3'!W13:AH32 + '1.1.4.4'!W13:AH32 + '1.1.4.5'!W13:AH32 + '1.1.4.6'!W13:AH32 + '1.1.4.7'!W13:AH32 + '1.1.4.8'!W13:AH32 + '1.1.4.9'!W13:AH32 + '1.1.4.10'!W13:AH32"

Change the formula. I supect that you have over run the allowed size for a formula. I had the same problem when I set up a loop to create a formula which then died after the weeks overan the available space.

Excel allows summing in the format
sum( sheet1!:sheet99!W13: AH32), you will have to find the exact format.

In summary it allows you to define the leftmost sheet, then the rightmost sheet and then the common cells.

If it won't allow multiple cells then sum each sheet and sum through the single total cells.

Thanks for the suggestion. Unfortunately, the pages that I am summing are not consecutive.