How to Assign Variable Name to Worksheet Object Using Its Code Name

Hi, newbie here...

I see in various tutorials that worksheets can be referred to by number, or name, or code name. Suppose I have a worksheet named "Data" whose code name is Sheet3. I'd like to assign a variable name (let's say "MySheet") to this worksheet, so that I can simply use MySheet in my macro instead of typing ThisWorkbook.Worksheets("Data"). But I would also like my macro to always refer to that sheet (even if the user moves it or renames it), so I'd like to define MySheet using the code name rather than the worksheet name (Data). Unfortunately, when I use code like this:

Please Login or Register  to view this content.

I get a compile error (Method or data member not found)! How can Sheet3 be "not found" when it's right there in the workbook? If I use this:

Please Login or Register  to view this content.

it works fine. But I want to use the code name rather than the worksheet name! What am I doing wrong?? Don't Worksheets("Data") and Sheet3 both refer to the same object?!?

Try this

Please Login or Register  to view this content.

Originally Posted by vlookup.

Try this

Please Login or Register  to view this content.

But doesn't that refer to the second worksheet (in the current order)? So if the user moves one of the worksheets, the macro could then refer to the incorrect sheet, right? I don't think the sheet number, and the number in the code name, refer to the same thing.

It appears that you're correct. Sorry.

The following worked for me, even when I changed the order of the sheets:

Please Login or Register  to view this content.

OK, thanks, that's a step in the right direction! Now how do I ensure that (if there are multiple workbooks open) Sheet2 always refers to the Sheet2 that's in "ThisWorkbook"? Is that the default if the specific workbook isn't referenced?

I assume so. If it required that the workbook be specified, then this code would return an error.