Renaming Sheets and Coding

Important Notice


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.

    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

    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.

    Code
    Worksheets("Data").Move After:=Worksheets(Worksheets.Count)


    which would move the names worksheet after all worksheets.


    You could look up the codename for the worksheet (assuming this is Sheet1) and use

    Code
    Sheet1.Move  After:=Worksheets(Worksheets.Count)


    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.


    Code
    Dim WS As Worksheet


Set WS = Sheets("Data")


    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.


    Code
    WS.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

    Code
    Set WS = Nothing

    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:

    Quote

    You 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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login