SUM same range from multiple workbooks

[caroline]

I am trying to sum the same range from 10 different workbook. I write the
name of each workbook in specific cells. But I am a little bit stuck to get
the right formula. Can you help?

Dim i%

Dim W As Range
Dim asRangeNamesW(1 To 10)
asRangeNamesW(1) = "workbook1name"
asRangeNamesW(2) = "workbook2name"
asRangeNamesW(3) = "workbook3name"
asRangeNamesW(4) = "workbook4name"
asRangeNamesW(5) = "workbook5name"
asRangeNamesW(6) = "workbook6name"
asRangeNamesW(7) = "workbook7name"
asRangeNamesW(8) = "workbook8name"
asRangeNamesW(9) = "workbook9name"
asRangeNamesW(10) = "workbook10name"

For i = 1 To UBound(asRangeNamesW)

Set W = Range(asRangeNamesW(i))

How do I do the rest?
something like
sum( Workbooks(W.Value).Worksheets("sheet1").Range("k5:AA5"))


Thanks

 

[Tom Ogilvy]

Dim i%
Dim tot as Double
Dim W As Workbook
Dim asRangeNamesW(1 To 10) as String
asRangeNamesW(1) = "workbook1name"
asRangeNamesW(2) = "workbook2name"
asRangeNamesW(3) = "workbook3name"
asRangeNamesW(4) = "workbook4name"
asRangeNamesW(5) = "workbook5name"
asRangeNamesW(6) = "workbook6name"
asRangeNamesW(7) = "workbook7name"
asRangeNamesW(8) = "workbook8name"
asRangeNamesW(9) = "workbook9name"
asRangeNamesW(10) = "workbook10name"

tot = 0
For i = 1 To UBound(asRangeNamesW)

Set W = Workbooks(asRangeNamesW(i))
tot = tot + application.Sum(w.worksheets("Sheet1").Range("K5:AA5"))
Next i

msgbox Tot

Assumes all 10 workbooks are open.