Refer to Range in Another Workbook

I think you have to qualify the sheet because its in an external workbook.

Please Login or Register  to view this content.

I don't think code names are allowed to reference sheets in an external workbook. You could use the sheet tab name (as I use in my previous reply) or the sheet index number. Alternatively, set a worksheet variable when you open the BudgetFile.

Originally Posted by Merf

I avoid using sheet tab names or sheet numbers, since users can change those (and then my macros won't work properly).
I like your idea of using a worksheet variable. Could I do that (using the sheet codename) right after opening the file (when it's still the active workbook):

Please Login or Register  to view this content.

or would I still need to use the tab name or sheet number?

You still couldn't directly use a code name for an external workbook sheet regardless if it's the active workbook.

Here's a kludge around it using the sheet code name.

Please Login or Register  to view this content.

Alternatively, you could loop through all the sheets in BudgetFile and check for a specific value in a cell to determine which one is the MainSheet.

You can use a codename in another workbook directly, but only by setting a reference to that workbook's project.

Originally Posted by xlnitwit

You can use a codename in another workbook directly, but only by setting a reference to that workbook's project.

That's what I suggested, but I don't consider that being Directly.

Where did you suggest that? I can't see that anywhere.

Please Login or Register  to view this content.

Ah, that's not what I mean. I mean set a reference using Tools- References (you have to change the project name from the default VBAProject) and you can then directly refer to the sheet using projectname.codename.