linking sheets
 

Dim charCode as Variant, arCharCode as Variant
Dim sStr as String, sStr1 as String
Dim wkbk as Workbook, wkbk1 as Workbook
Dim sPath as String
sPath = "C:\Myfolder\MyData\"
arCharCode = Array(
"A10490",
"B12368",
"C33345",
. . . ,
"DD1036")

for each charcode in arCharCode
sStr = "Targetfile " & charcode & " 03 2004.xls"
sStr1 = "Sourcefile " & charcode & " 03 2004.xls"
set wkbk = workbooks.open( sPath & sStr)
set wkbk1 = workbooks.open( sPath & sStr1)
wkbk.Worksheets(1).Range("R14:R75").Value = _
wkbk1.Worksheets(1).Range("R14:R75").Value
wkbk1.close SaveChanges:=False
wkbk.Close SaveChanges:=True
Next

code is untested and may contain typos.

--
Regards,
Tom Ogilvy

"Jem" wrote in message
...
I need to arrange for a collection of workbooks to be updated from data
contained in another collection of workbooks as follows:


Targetfile A10490 03 2004.xls needs to have range R14:R75 populated by the
data in Sourcefile A10490 03 2004.xls.

The same action then needs to be performed for another 29 pairs of
workbooks with identical worksheet names except for the 6 character code
(A10490) in the middle of the name. This code is always the 19th -24th
characters in the workbook name.