Macro code to reference workbooks

[aa2000]

Hi all

As the title says, I'm trying to reference one workbook from the macro code of another workbook.
The macro code is comparing values on 2 workbooks and the one in which the code is written is the active workbook.

Set Erng2 = ActiveWorkbook.Sheet5.Range("R1:R50")
Set Erngr2 = Workbooks(Filename.xls).Sheet6.Range("A1:A20")

Is this the correct code to reference the two workbooks?

Cheers

 

[VoG]

Perhaps

Set Erngr2 = Workbooks("Filename.xls").Sheet6.Range("A1:A20")

 

[aa2000]

Do I have to put the full file path along with the file name?

Cheers

 

[VoG]

No, not if the file is open.

 

[aa2000]

When I run this sub I get Run time error 438 "Object Doesn't support this property or method" and that line is higlighted. Any ideas why?

Cheers

 

[VoG]

You are right - it doesn't seem to like using sheet codenames for other than the active workbook.

Maybe

Set Erngr2 = Workbooks("Filename.xls").Sheets(6).Range("A1:A20")

 

[aa2000]

That does work fine for the workbook I am using to test these codes on, but when I try it on the actual workbook, which was created in germany, on a german version of xp, it fails. I presume its due to me not knowing the plural of sheets in german

Anyway thank you for you help!