Erroneous reserved name error? RRS feed

  • Question

  • I have an application in Excel 2010/2013 that creates worksheets from a table of data and distributes data from the table throughout the worksheets.  It names the worksheets and assigns a name to A1 of each sheet based on column headers.

    I am chemist, so many of the names are Element related (1 or 2 letters and potentially a number appended).  Excel won't let me use "Sb3", throwing an error about Excel built-in name or duplicate name. 

    Easy to simulate manually:
        Create a new workbook.
        Rename sheet 1 "Sb".  Create a Name for A1 in Name manager "Sb"
        Add a sheet and rename to "Sb3".  Create a Name for A1 in Name manager "Sb3".
        Error generated.

    Note that "Sb" can be used as both a worksheet and a range name; "Sb3" can be used as a worksheet name but not a range name.  There are usually 30 other worksheets where the same worksheet and range name are permitted.

    Wednesday, July 27, 2016 7:40 AM


  • I'll try to explain with the following examples.

    Range("B1").Name = "MyName"     'Easy VBA method to name a range

    Range("MyName").Select          'Select the named range and it goes to cell B1

    'Following code will select cell SB3
    Range("Sb3").Select     'Note case is ignored and B is same as b

    'If a range was named Sb3 then the code to select would have to be
    Range("Sb3").Select     'This is exactly the same as selecting cell SB3 hence can't name range same as cell address

    Regards, OssieMac

    Wednesday, July 27, 2016 10:55 AM