data consolidation using vba
 

I have a complex workbook and i would like to use the data /
consolidation functionality to combine information from a number of
worksheets. The names of these worksheets are dynamic.

I have created an array (mystr) with the worksheet names and when i do
a msgbox on the array the information is as follows:

'H:\[2009 Jan to Jun Books.xls]Exp Jan 24 25'!R33C1:R100C2", "'H:
\[2009 Jan to Jun Books.xls]Exp Jan 31 1'!R33C1:R100C2", "'H:\[2009
Jan to Jun Books.xls]Exp Feb 7 8'!R33C1:R100C2", H:\[2009 Jan to Jun
Books.xls]Exp Feb 14 15'!R33C1:R100C2"

I then do:

Sheets("" & emname & "").Select 'select current expenses sheet
Range("A33").Select

Selection.Consolidate Sources:=mystr, Function:=xlSum, _
TopRow:=False, LeftColumn:=True, CreateLinks:=False

The error message I receive is:

"Run-time error: '1004':
Consoliate method of range class failed

I would appreciate any input.

Thanks