Kenneth's first question is all important; if you want to copy over values and formats etc. you'll need the source file to be open, if you want just values then it doesn't need to be opened.
The following is only part of the answer, being one way to copy the right cells to the right places; we need to know how you're going to get the list of files/sheetnames.
Anyway:[VBA]Sub blah()
FileNm = "='C:\Test\My Documents\[test.xls]Sheet1'!"
For destrow = 5 To 7 'three rows of same source data for the time being
sourcerow = 9
For destcolm = 4 To 42 Step 2
With Cells(destrow, destcolm)
.FormulaR1C1 = FileNm & "R" & sourcerow & "C3"
'.Value = .Value 'optional; removes formula.
End With
sourcerow = sourcerow + 1
Next destcolm
Next destrow
End Sub
[/VBA]