I wrote some code recently and then renamed the sheet and the code stopped working. My question is
How do I ensure that renaming the Sheets does not affect my coding. For example if I renamed sheet 1, Data.
Is there a best practice in how to write the code to avoid this problem
Renaming Sheets and Coding
Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.
-
-
-
Re: Renaming Sheets and Coding
Hi, oracle259,
as long as you stay in the very same workbook you should encounter no problem in not using the sheet name like it is indicated on the worksheet tab, i.e.
which would move the names worksheet after all worksheets.You could look up the codename for the worksheet (assuming this is Sheet1) and use
This would move the sheet with the codename no matter what will be displayed on the worksheet Tab.The codename for a sheet may only be altered in the VBE via properties or by VBA code.
HTH,
Holger -
Re: Renaming Sheets and Coding
You can also set the worksheet in memory, then the name doesn't matter.
i.e.
now you can refer to that sheet as WS in your code, regardless of what the name gets changed to, as long as the name was correct when it was set.
CodeWS.Range("A1").Value = "Example" WS.Name = "Renamed" WS.Move After:= Sheets(Sheets.Count) WS.Range("B1").Value = "Still Works!"
at the end of your sub, use
to remove the sheet from memory, this is good practice as it can stop unforeseen issues in later code, especially if the variable is Public.
-
Re: Renaming Sheets and Coding
Hi, S O,
please clarify:
QuoteYou can also set the worksheet in memory, then the name doesn't matter
You would need the correct sheet name in order to get your sample code working.If you rely on the codename you must not set an object to that codename as it already exists and may be addressed.
Sample:
your code works fine if the sheet is named "Data". You would need to alter the code if soembody renamed the sheet to "Test". Am I correct about this?At any case if the codename for the mentioned sheet was Sheet1 the code would work, no matter how the sheet would be named on the worksheet tab.
Ciao,
Holger -
Re: Renaming Sheets and Coding
Hi Holger,
Quote...as long as the name was correct when it was set.
I'm not disputing your answer, you are entirely right - the codename is a more failsafe way of referencing a worksheet in code.
I've found in my experience however that quite a few people don't understand the concept of the code name or how to go about finding it. I'm merely supplying an alternative in case the OP doesn't know the codename, as setting a sheet object by name still stores it using the index value, but you are effectively searching for the sheet's index by using a string.
-
-
-
Re: Renaming Sheets and Coding
Quote from royUK;720343Roy, as ever - your site is a goldmine! :smile:
-
Re: Renaming Sheets and Coding
Thanks SO
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!