|re: "Hope I have not confused you any further"|
One thing that is confusing me is your use of the words "workbooks" and "worksheets".
You use Workbook1.xls which is a file name and Worksheet2.xls which is also a file name, but slightly confusing since you change the naming structure. Then you say that R1 in each workbook has to be assigned values "to correspond to the worksheets".
Maybe it's me, but I'm not getting a clear picture. What worksheets, in what workbooks?
Just to be clear, I'll state something that may be obvious, but will at least get us to the same starting point:
A workbook is the term used for the Excel file itself, which must contain at least one worksheet. Of course, a workbook can contain more than one worksheet.
Maybe if you tried explaining it again, making very clear distinctions between workbooks and worksheets and how they relate to range names and data, I might get it.
"In Workbook1, Sheet1!A1:A5 is named R1. In Workbook2, Sheet1!B1:B5 is also named R1. I need..."