Best way to fiigure out the proper sheet to reference cells in a different workbook

[Ed_Collins]

Greetings,

I'm fond of referencing cells in sheets by using the sheet's CodeName whenever possible. This method is preferable to me because the user, at some point in the future, might rename the sheet. For example, I don't want to refer to the "Summary" tab specifically by that name, because it might not always be named "Summary."

I also don't wish to refer to the sheet's index, since the user might also add or remove sheets at some point. And I've learned that referencing the sheet by its CodeName solves these problems.

However, I just recently learned you can't reference a sheet by its CodeName if the sheet is in a different workbook than the one in which the code resides. And sure enough, all of the attempts I've tried to do just that have failed. So, in that case, what's the best way to find/reference a particular sheet?

For example, I'm attempting to reference a sheet in another workbook. The data I want is currently on the sheet that has a CodeName of Sheet9. The user's workbook tab name is called "Summary" and right now it happens to be the 17th sheet in the other workbook. In my program this works:

data$ = wb.Worksheets(18).Cells(1, 1)

and this works too:

data$ = wb.Worksheets("Summary").Cells(1, 1)

But as mentioned above, I'd rather not user "Summary" or use the index. So what's the preferred method for something like this? (The only thing I can think of is to loop through all of the sheets, looking for something specific that I expect to find on this sheet and this sheet alone, and once I find it then I will know THAT's the sheet I want.)

As always, thanks in advance for your reply.

[Philb1]

Hi Ed.. This is how I do it
Put an empty workbook named TESTER.xlsx in your downloads folder to test this code. Then copy & paste the code below into an empty module & play with it.

Please Login or Register  to view this content.

Hope it's of use
Phil

[:) Sixthsense :)]

You stated every possibilities neatly and I don't think there is any other go