Updating VBA code if sheet name changes

  • Hi All,
    I have the following code in a workbook which copies all the data from several worksheets onto one. The code works fine until somebody changes the quote, quote (1), etc names on the worksheet tabs. Is there a way of automatically updating the vba code if the worksheet name changes.


    Thanks





  • Re: Updating VBA code if sheet name changes


    Use he Sheets Code name in your code instead.


    Have a look in the VBE > Project explorer .... you will see


    Sheet1(Sheet1)
    Sheet2(Sheet2)


    Names in brackets are Tab sheet names you enter/change
    The 1st name is the code name and does not change unless done
    by code or via the applications screen..... just lock the project for protection.

  • Re: Updating VBA code if sheet name changes


    You are better off writing code that refers to sheets by their static name as opposed to their given name.... take this code


    Code
    Worksheets("Sheet1").activate
    sheets(1).activate
    Sheet1.Activate


    Essentially, they all look the same, however they are quite different.


    The first code is what you are doing now, referring to a sheet via the tab name... so if someone changes your sheet tab name, your code errors


    The second code refers to the first sheet in the sheets collection... i.e. the worksheet to the far left hand side of all the tabs - so this could be any sheet, it just depends on the position.


    The third code is I think what you would be best using - it uses the object name, as opposed to the "tab" name. You will notice that in the VBE, when you first open a workbook, both the tab names & the object names are the same. If you change the tab name for Sheet1 to something else, say MySheet, in the VBE you will notice that the two "names" now differ....


    You have a (Name) field in the worksheet properties of Sheet1 and a Name field called MySheet


    To refer to this sheet object, you can still use


    Code
    Sheet1.Activate


    but


    Code
    Worksheets("Sheet1").Activate

    will no longer work, you need to use


    Code
    Worksheets("MySheet").Activate


    Hope this helps explain things

  • Re: Updating VBA code if sheet name changes


    Hi


    A small question on the same topic...


    If i have multiple workbooks defined with different names, say one of the being wkbook


    Now neither of the below codes, when replaced to the new format(in bold) work


    Code
    Set wksheet = wkBook.Sheets("ReportSheet")
    [B]Set wksheet = wkBook.Sheet1[/B]
    
    
    wksheet.Activate
    [B]wkbook.Sheet1.Activate[/B]



    Aadarsh

  • Re: Updating VBA code if sheet name changes


    Read Dave's article. It specifically mentions


    Quote


    One Draw back
    The only times you cannot use a sheets CodeName is when you reference a Sheet that is in a different Workbook to the one that the code resides.

Participate now!

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