I _think_ that this works:

Option Explicit
Sub testme()
Dim wkbk As Workbook
Dim consWks As Worksheet
Dim i As Long
Dim myCell As Range
Dim InputAddr As Variant
Dim OutputAddr As Variant
Dim CellCtr As Long
Dim AreaCtr As Long

InputAddr = Array("J5:J82", "L5:L82", "M5:M82")
OutputAddr = Array("H4", "I4", "J4")

Set consWks = ActiveSheet

With Application.FileSearch
.NewSearch
.LookIn = "C:\Folder"
.LookIn = "C:\my documents\excel\test"
.SearchSubFolders = False
.Filename = "*.xls"
.MatchTextExactly = True
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
MsgBox "There were " & .FoundFiles.Count & " file(s) found."
Application.EnableEvents = False
For i = 1 To .FoundFiles.Count
Set wkbk = Workbooks.Open(Filename:=.FoundFiles(i), _
UpdateLinks:=0)
For AreaCtr = LBound(InputAddr) To UBound(InputAddr)
CellCtr = -1
For Each myCell In wkbk.Worksheets("sheet1") _
.Range(InputAddr(AreaCtr)).Cells
CellCtr = CellCtr + 1
consWks.Range(OutputAddr(AreaCtr)) _
.Offset(CellCtr, 0).Value _
= consWks.Range(OutputAddr(AreaCtr)) _
.Offset(CellCtr, 0).Value _
& myCell.Value
Next myCell
Next AreaCtr
wkbk.Close savechanges:=True
Next i
Application.EnableEvents = True
Else
MsgBox "There were no files found"
End If
End With
End Sub

Take a look at VBA's help for workbooks.open and you'll see what updatelinks:=0
means and to stop the workbook_open events, we just turn off .enableevents.



Joe D wrote:

Thank You Dave,

That does exactly what I want. However, it does somethings I don't want
as well:

1) I forgot about the fact that all of the workbooks that I open have
links in them that I need to keep, and for every workbook that gets
opened a dialog box asks me if I want to update them and I do want to
update them (well, actually it doesn't matter for my purposes).

2) I also forgot that they all have a userForm that automattically pops
up when each one is opened, and then I have to manually close each one.

How do I modify that code so that I don't have 80 different windows
bugging me each time I run it?

I have another question. When I explained my situation, I simplified
for clarity. I really need to reference cells J5 through J82, L5
through L82, and M5 through M82 and place them correspondingly in the
constant worksheet in H4:H81, I4:81, and J4:81. I tried adapting the
code for my purposes with no luck. What is the best way to do that?

Thanks,
Joe

--

Dave Peterson