Dynamic Arrays and Formulae
 

Anyone? Still pulling my hair out here.

Cheer

--
Josep

-----------------------------------------------------------------------
Joseph's Profile: http://www.excelforum.com/member.php...nfo&userid=563
View this thread: http://www.excelforum.com/showthread.php?threadid=31387

Dynamic Arrays and Formulae
 

Joseph,

Try this one:

Sub SumWorkbooks()
Dim MyCell As Range
Dim wb As Workbook
Dim MyFormula As String
Set MyCell = ThisWorkbook.Sheets("Reports").Range("B2")

For Each wb In Workbooks
If wb.Name < ThisWorkbook.Name Then
MyFormula = MyFormula & "'" & wb.Name & "'" & "!R2C2" & ","
End If
Next wb
MyCell.FormulaR1C1 = "=SUM(" & Left(MyFormula, Len(MyFormula) - 1) & ")"
End Sub

Regards,
KL

"Joseph" wrote in message
...

Anyone? Still pulling my hair out here.

Cheers


--
Joseph


------------------------------------------------------------------------
Joseph's Profile:
http://www.excelforum.com/member.php...fo&userid=5637
View this thread: http://www.excelforum.com/showthread...hreadid=313874