Referencing to sheet (name) property of other files

[Ronnet2]

Hi All,

Recently I embraced to use of VBA sheet (name) propety which allows me to reference sheets without the use of 'Sheets("Sheet label").range("A1")' but instead directly refer to the sheet name in VBA as in: 'Sheetname.range("A1")'. This allows end users to rename sheets without breaking the code.

Now for my question / issue. When I reference sheets named in VBA in other workbooks then I get error '438': Object doesn't support this property or method. For example, I reference: OpenedSourceFile.Sheetname.Range("A1") and I get this error but when I reference OpenedSourceFile.Sheets("Sheet label").Range("A1") then it works. It seems this more sophisticated methode of referencing sheet doesnt work across different workbooks.

Am I correct to assume that I can't reference the sheet (code) name? And are there any alternatives? I would hate to go back to Sheets("sheet label") since I know end users will start switching sheets around and renaming them at some point.

[GC Excel]

Hello,

To my knowledge, you cannot use a sheets CodeName when you reference a Sheet that is in a different Workbook to the one that the code resides.
But here's a possible workaround:

http://yoursumbuddy.com/using-worksh...her-workbooks/

[AlphaFrog]

Am I correct to assume that I can't reference the sheet (code) name?

Correct. You cannot reference a sheet in an external workbook as a child object of that workbook using it's code name.

are there any alternatives? I would hate to go back to Sheets("sheet label") since I know end users will start switching sheets around and renaming them at some point.

This syntax is a round about way to reference an external workbook's sheet by its code name "Sheet1"

Please Login or Register  to view this content.