What's the most "stable" way to activate or select a sheet?

Hi,

I have a really newbie question. What is the most consistent error-free "stable" way to refer to sheets and select or activate them? I have various macros that jump the workbook user across sheets.

Is it better, for example, to refer to a sheet as "Sheet1" or the sheet name (e.g. "HOME")? Is there a functional difference?

Is there something better to do for selecting or activating a worksheet or range than just writing:

Please Login or Register  to view this content.

I ask because I occasionally get the 1004 method of selection failure error message. My macro runs a bunch of code (adding protections, hiding things etc.) when the workbook is opened and macros are enabled. The very last thing it does is jump the user back to a home screen. The Debug highlights the sheet selection for this chunk of code:

Please Login or Register  to view this content.

I KNOW the code is not incorrect because it usually runs fine. I can't usually replicate the error but I need the error to never appear because users will get confused.

Any thoughts on why I get these errors sometimes and how to prevent them? Thank you so much in advance!!!

Thank so much, Olly!! OK, I will try to only refer to the sheets by the codename rather than the sheet name.

Unfortunately, I have found it unavoidable to use select and activate? Some formula calculations on a certain page wouldn't work unless I activated the sheet first. And I use code to select a cell of a sheet to jump the user back to where they were before a macro ran (e.g. a macro might do copy/pasting/calculations etc. elsewhere in the workbook). I also use select for ranges often for macros that copy and paste ranges into other ranges. =/

I really appreciate your help!!!

Thanks for all your responses!!
@GeneralDisarray: Thanks! I was wondering that too, but when the worksheet selection error happened the sheet was definitely unhidden and correctly named so I was really mystified =/

I would love to avoid "activate" and "select" if those are prone to error as you all are suggesting. I just don't know how to avoid it and still do the things that I need the workbook to do - so I am super grateful for any/all advice!

Desired functions:

1. To jump users to the top of certain sheets. There might be multiple people using the same workbook file and users may save the workbook while halfway scrolled down to the bottom of a random sheet. Every time they select a sheet, I want them to be at the top so they don't get confused. I'm using this code which is located in every Microsoft Excel Objects Sheet:

Please Login or Register  to view this content.

I also used the code that errored on me (in my original post) to select the "HOME" sheet so that every time a user opens the file anew they land on their familiar home sheet instead of whatever random sheet they or someone else had been working in when they save/closed the file the last time.

2. To jump users across sheets. The workbook has 23 sheets and users may not be Excel savvy. I created buttons/macros so that they can jump from sheet to sheet without looking for the tab. Here's an example of code for one of the "jump" button macros:

Please Login or Register  to view this content.

3. I am generating reports that involve copy/pasting of various ranges. I don't know how to avoid selecting the sheet and selecting the range to run the reports. I'm linking to a fraction of the file to demo what I mean (see "Patient Reports" sheet row 326 and hit the Calculate/Refresh button). Here's the beast mess of code (I'm sure I have a ton of inefficiencies/bad practices!) attached to that button:

Please Login or Register  to view this content.

I had trouble with the upload function in the message board so I uploaded the demo file to my Google Drive instead so it should be downloadable through this link: https://drive.google.com/file/d/0Bwe...ew?usp=sharing

THANK YOU so much in advance!!

Another issue came up when I tried to replace all worksheet names with the backend names (e.g. "Sheet2") as suggested: now I'm getting Run-time error '438': Object doesn't support this property or method.

It's getting angry at this code (maybe because it's part of a statement referencing a ListObject?):

Please Login or Register  to view this content.

Necessary to stick with the worksheet name for these types of statements?

Sorry for all the questions!

@bulina2k Awesome, thank you so much for posting this code improvement!!! I will definitely test it out - it looks so much more efficient

Thanks so much @snb, and again bulina2k! I implemented this code for the above section and it worked great. It's awesome how few lines of code this needs!

I'm still trying to figure out how to handle some of the rest of the scenarios without using selection but this is an awesome start to work from!! You guys rock!

Thanks for the suggestions re: Hyperlinks, Neil_ & snb! I unfortunately haven't had the best of luck with those. I originally hyperlinked a bunch of images of buttons for the user interface and jumping across sheets, but one weird quirk I found was that if the previous click was selecting something (e.g. a radio button or check box), the hyperlink would not work until the user clicked away to something else. Macros fixed that for me and I've stayed away from hyperlinks ever since.

What I have done though - in case the .Activate or .Select code goes haywire again for no discernible reason after functioning just fine many times - is to add some error code. Now all my jump code looks something like this:

Please Login or Register  to view this content.

I'd really just like to know why those commands are fundamentally so glitchy, but since it just seems like a given limitation I'm trying to avoid the error pop up messages for users with the On Errors (for non-essential instances) and also keeping their use to a minimum with the new code you all showed me above. My ideal given the glitchiness is to only use the .Activate and .Select for my jump buttons - since that won't really screw up any calculations or data. =/

Thanks again, so much!!