Hi,

I have a workbook that opens other workbooks by date and then concatenates a formula before dropping it into a cell and then autofilling across a range. Problem is, as more and more workbooks are added the formula is getting bigger. I heard somewhere that the longest formula you can have in a cell is 1024 characters. Is there any way to split a formula across a few cells and then total up the results? This is my code so far to make the formula:

Sub populate()

Application.ScreenUpdating = True
sheets("reports").Select
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) & ")"
MsgBox (Len(myformula))

End Sub


Any help is much appreciated.