Python Macro

Writing a macro in Python for renaming cells turned out to be simple. Use of the dir command and just trying various options helped.

def chgSheet1RangeNames( ):
    """This macro changes names to the Excel values"""
#get the doc from the scripting context which is made available to all scripts
    model = XSCRIPTCONTEXT.getDocument()
    names = model.NamedRanges
    name_list = [names.getByIndex(i).getName() for i in range(names.getCount()) 
        if names.getByIndex(i).getName().startswith('sheet1')
        and names.getByIndex(i).getName().endswith('_2')]
    for n in name_list:
        rng = names.getByName(n)
        rng.setName(n[:-2])
    return None

I selected the names which needed to be renamed and renamed them one at a time.

Unfortunately, some hidden functions gave errors. So, the best option was to create additional names for the same ranges. This was also easier than expected. Instead of

rng.setName(n[:-2])

I used

names.addNewByName(n[:-2], rng.getContent(), rng.getReferencePosition(), 0)