Hi,

I have a number of workbooks in a folder (number varies) which I have to
open and add the values of the same range ("F15:M34") in each workbook into
a summary workbook.

The code below works, but to me appears long winded, is there an easier more
efficient way of writing this code. This is the first time I have had a go
at using arrays.

thanks in advance
Neil

Dim myValues(160) As Long
Dim MyPath As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
MyPath = ActiveWorkbook.Path
On Error Resume Next
Set wbCodeBook = ThisWorkbook

With Application.FileSearch
.NewSearch
.LookIn = MyPath
.FileType = msoFileTypeExcelWorkbooks
If .Execute 0 Then
For lCount = 2 To .FoundFiles.Count
Set wbResults =
Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)

For A = 1 To 20
myValues(A) = myValues(A) +
ActiveSheet.Range("F" & 14 + A).Value
Next A
For B = 21 To 40
myValues(B) = myValues(B) +
ActiveSheet.Range("G" & -6 + B).Value
Next B
For c = 41 To 60
myValues(c) = myValues(c) +
ActiveSheet.Range("H" & -26 + c).Value
Next c
For d = 61 To 80
myValues(d) = myValues(d) +
ActiveSheet.Range("I" & -46 + d).Value
Next d
For e = 81 To 100
myValues(e) = myValues(e) +
ActiveSheet.Range("J" & -66 + e).Value
Next e
For f = 101 To 120
myValues(f) = myValues(f) +
ActiveSheet.Range("K" & -86 + f).Value
Next f
For g = 121 To 140
myValues(g) = myValues(g) +
ActiveSheet.Range("L" & -106 + g).Value
Next g
For h = 141 To 160
myValues(h) = myValues(h) +
ActiveSheet.Range("M" & -126 + h).Value
Next h

'Close the workbook you opened
wbResults.Close SaveChanges:=True

Next lCount
End If
End With
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
For i = 1 To 20
ActiveSheet.Range("F" & 14 + i).Value = myValues(i)
Next i
For j = 21 To 40
ActiveSheet.Range("g" & -6 + j).Value = myValues(j)
Next j
For k = 41 To 60
ActiveSheet.Range("h" & -26 + k).Value = myValues(k)
Next k
For l = 61 To 80
ActiveSheet.Range("i" & -46 + l).Value = myValues(l)
Next l
For m = 81 To 100
ActiveSheet.Range("j" & -66 + m).Value = myValues(m)
Next m
For n = 101 To 120
ActiveSheet.Range("k" & -86 + n).Value = myValues(n)
Next n
For o = 121 To 140
ActiveSheet.Range("l" & -106 + o).Value = myValues(o)
Next o
For p = 141 To 160
ActiveSheet.Range("m" & -126 + p).Value = myValues(p)
Next p

no worries, I have worked it out.

Set chkRange = ActiveSheet.Range("F15:m34")
For A = 1 To 160
myValues(A) = myValues(A) + chkRange(A).Value
Next A

"Neil Eves" wrote in message
...
Hi,

I have a number of workbooks in a folder (number varies) which I have to
open and add the values of the same range ("F15:M34") in each workbook
into a summary workbook.

The code below works, but to me appears long winded, is there an easier
more efficient way of writing this code. This is the first time I have had
a go at using arrays.

thanks in advance
Neil

Dim myValues(160) As Long
Dim MyPath As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
MyPath = ActiveWorkbook.Path
On Error Resume Next
Set wbCodeBook = ThisWorkbook

With Application.FileSearch
.NewSearch
.LookIn = MyPath
.FileType = msoFileTypeExcelWorkbooks
If .Execute 0 Then
For lCount = 2 To .FoundFiles.Count
Set wbResults =
Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)

For A = 1 To 20
myValues(A) = myValues(A) +
ActiveSheet.Range("F" & 14 + A).Value
Next A
For B = 21 To 40
myValues(B) = myValues(B) +
ActiveSheet.Range("G" & -6 + B).Value
Next B
For c = 41 To 60
myValues(c) = myValues(c) +
ActiveSheet.Range("H" & -26 + c).Value
Next c
For d = 61 To 80
myValues(d) = myValues(d) +
ActiveSheet.Range("I" & -46 + d).Value
Next d
For e = 81 To 100
myValues(e) = myValues(e) +
ActiveSheet.Range("J" & -66 + e).Value
Next e
For f = 101 To 120
myValues(f) = myValues(f) +
ActiveSheet.Range("K" & -86 + f).Value
Next f
For g = 121 To 140
myValues(g) = myValues(g) +
ActiveSheet.Range("L" & -106 + g).Value
Next g
For h = 141 To 160
myValues(h) = myValues(h) +
ActiveSheet.Range("M" & -126 + h).Value
Next h

'Close the workbook you opened
wbResults.Close SaveChanges:=True

Next lCount
End If
End With
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
For i = 1 To 20
ActiveSheet.Range("F" & 14 + i).Value = myValues(i)
Next i
For j = 21 To 40
ActiveSheet.Range("g" & -6 + j).Value = myValues(j)
Next j
For k = 41 To 60
ActiveSheet.Range("h" & -26 + k).Value = myValues(k)
Next k
For l = 61 To 80
ActiveSheet.Range("i" & -46 + l).Value = myValues(l)
Next l
For m = 81 To 100
ActiveSheet.Range("j" & -66 + m).Value = myValues(m)
Next m
For n = 101 To 120
ActiveSheet.Range("k" & -86 + n).Value = myValues(n)
Next n
For o = 121 To 140
ActiveSheet.Range("l" & -106 + o).Value = myValues(o)
Next o
For p = 141 To 160
ActiveSheet.Range("m" & -126 + p).Value = myValues(p)
Next p