Using codenames in VBA?

From what I’ve learned about the pros/cons of using codenames in VBA, I’m inclined to use them. But I would appreciate any comments pro or con from the experts here that feel strongly on that matter.
I am having a problem using codenames to qualify a range however. In the Immediate Pane of the VBA editor,

?activesheet.codename returns cnEarnFlags

cnEarnFlags.Range("templaterow").Copy will copy the proper range, but

activesheet.codename.Range("templaterow").Copy returns a Run-time error 424 Object required. Since this is only a substitution/combination of the first two commands, I thought it would work.

I need this code in the “Thisworkbook” page of my workbook. Appreciate any ideas. Thanks.

Why codename for ActiveSheet?

Please Login or Register  to view this content.

See my posts in http://www.ozgrid.com/forum/showthread.php?t=175785

leelnich - Thanks for the reply. That gives me a Run-time error 9 - Subscript out of range

jindon – I have a dozen worksheets which will use the same code. I’m using the codename to differentiate which worksheet triggered the code.

Please Login or Register  to view this content.

I was testing using the Activesheet – the actual code will use the Sh object above. But it still throws the same error.

Have you read the link?

Please Login or Register  to view this content.

Yes, but I didn't see how it applied. I just looked at it again, and am still not sure how to use it. Perhaps I didn't explain my question well. All of the codenames above "cnXXX" have a range named "templaterow" defined as a workheet level name. Currently I have the same code in each worksheet in a SelectionChange sub which I'm trying to consolidate into the Thisworkbook object (i.e. 13 copies of the same code into 1). Also trying to use the codename property vs. the worksheet name. This is the worksheet code I'm trying to replace from one of the individual worksheets:

Please Login or Register  to view this content.

Does this help explain my question?

Again, have tried the code I have posted?
If you know how to set the worksheet using CodeName, the rest should be very easy.

Please Login or Register  to view this content.

Then you can after the Select statement

Please Login or Register  to view this content.

Did you post the link you intended? The code on the link you provided adds a new worksheet my workbook (not what I'm trying to do) and does not include a Select Case statement. Sorry, maybe I'm just completing misunderstanding you, but now I have tried your code and am still confused. I appreciate your effort.

Originally Posted by jindon

Have you read the link?

Please Login or Register  to view this content.

Above is the code I was referring to.

I apologize - I must be more tired than I thought - I missed that post. I have added it now and it's throwing a 1004 Error "Programmatic access to Visual Basic Project is not trusted"