Hello Excellers and welcome back to another blog post in my #Excel tips and #MacroMonday 2019 series.   Do you every find that your Excel  macro does not work after you have changed the name of a specific worksheet?.  So, how annoying is that right?.  So, do you go into your VBA code and change worksheet references.  Having to update to what you have renamed the sheet or sheets to?.  Right, you know what I am talking about.  

Well, I wish that I had known about this great Excel VBA trick a long time ago. Even if I change worksheet names say for example from My Data to Data by renaming it, my code will STILL work. Ok. let’s look at a live example.

Let’s Work Through An Example Macro.

Ok, so here is a simple Excel macro.  The VBA code below will print out the value in Cell A10 of my current worksheet which is named MyData.

The second last line of code Debug.Print TotalSales, outputs the result of the macro when ran, to the Immediate Window.  If  you want to read more about the Developer Area in Excel VBA then feel free to read my blog post below on this subject.

Macro Mondays – Moving And Undocking The Immediate Window In The Visual Basic Editor

As you can see in my screen shot below the Macro has resulted in the correct data being returned from cell B10.

The Macro Problem.

So, the problem occurs if the worksheet is renamed wither by you or indeed someone else using the Excel workbook. Say I now change the worksheet name from MyData to simply Data. Will the macro run?. No, it will not.

When I run the macro after I changed the worksheet name, I get a  Subscript Out Of Range Error. 

The Solution To The Macro Not Working.

So, now let’s take a look at the solution which will stop the code breaking even if the worksheet names are changed in our macro.  Instead of the using the ThisWorkbook.Worksheets collection we use the CODENAME of the worksheet.  Just what is the CODENAME you ask.

If you take a look at the Project Explorer in your VBA screen worksheets have 2 names. One of them in parentheses.  The name in parentheses is the sheet name we are used to.  The name to left of that is the CODENAME.

So, the solution is to use the CODENAME in our VBA code.  This does not change when you rename the worksheet names.  Also we can refer directly to the worksheet without the longer line of original code.  See below.

The real test is to first test the macro with the new code, then rename the worksheet back to MyData, and see if the code runs sucessfully again.  See what happens below.

How cool is that.  The code not care that the worksheet has been renamed it is using the CODENAME which is not easily amended, or is not affected by the user renaming the worksheet in the Excel user interface.  Phew.

What Next? Want More Tips?

So, if you want more tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

If you want to see all of the blog posts in the Macro Monday series. Click on the link below

Macro Mondays Blog Posts.

Do You Need Help With An Excel Problem?.

Finally, I am pleased to announce I have teamed up with Excel Rescue, where you can get help FAST.  All you need to do is choose the Excel task that most closely describes what you need to be done.  Above all, there is a money back guarantee and similarly Security and Non-Disclosure Agreements.  Try It!.  Need Help With An Excel VBA Macro?.  Of course, you don’t need to ask how to list all files in a directory as it is right here for free.