Multiple Named Ranges Code

[ExcelRoy]

Hi,

I have many, many named ranges to re-name, and have been fortunate to find a code that renames them all, only i cannot get it to work properly. I wonder if someone might take a look over the code to see if anything is wrong?

Basically you type the old name in one column and the new name in the next column. You then highlight old names and run macro?

It says subscript out of range?

Thanks

Sub BatchRename()
'Uses functions from JKP's Name Manager addin:
'www.jkp-ads.com/officemarketplacenm-en.asp

'Takes a list of names (selected cells on worksheet in the file).
'Renames those names with the name which is in a column to the immediate right
'of the selected range

    Dim sOldname As String
    Dim sNewName As String
    Dim oCell As Range
    Dim lRowCount As Long
    lRowCount = Selection.rows.Count
    Application.Run "'" & Workbooks("Name Manager.xla").FullName & "'!InitNameManager"
    For Each oCell In Selection.Cells
'ActiveSheet.UsedRange.Columns(1).SpecialCells(xlCellTypeVisible)
        If oCell.row > 1 Then
            sOldname = oCell.Value
            sNewName = oCell.Offset(, 1).Value
            Application.StatusBar = sOldname & ", " & sNewName & ", " & Format(oCell.row / lRowCount, "0%")
            If sOldname <> sNewName And sOldname <> "" And sNewName <> "" Then
                Application.Run "'" & Workbooks("Name Manager.xla").FullName & "'!replacename", sOldname, sNewName, True
            End If
        End If
    Next
    Application.StatusBar = False
    Application.Visible = True
End Sub

 

[Weaver]

Apologies if this is a dumb question, but have you installed 'name manager.xla'?

 

[ExcelRoy]

Hi Weaver,

Yes it is installed and works fine.

Any ideas why the code is not working correctly?

Many Thanks

 

[Weaver]

Looking at it, what row does your selected range start on, and does it contain a header?

At what point does the code fail?

 

[ExcelRoy]

These are the only instruction given

I can only assume you have to just list the 2 columns side by side and highlight the existing named ranges and the code will do the rest for you

9	Renaming many range names
Renaming a range name is possible with Name Manager, but if you have a lot of names to change, perhaps this method is simpler:

-	Create a two-column list, old names in first column, new names in second.
-	Select the cells in the first column and run this routine (assuming you have Name Manager installed!):

 

[ExcelRoy]

Hi Guys,

Maybe some kind person can re-write the code necessary

What i would like to do, is list out in one column (any column) the old named ranges and the next column (immediate right) the new named ranges

I would like to just highlight the ones i would like re-named and then run the code

example

Bob_Hours_1 Bob_Hours_001
Neil_Hours_1 Neil_Hours_001
Alan_Hours_10 Alan_Hours_010
Lee_Hours_12 Lee_Hours_012

Hope this makes sense

Thanks in advance

 

[ExcelRoy]

The original code is throwing up a Runtime 9 error

and is failing at the Application.Run line

Any thoughts?

 

[ExcelRoy]

Sorted:

Hi,

Rename the

Application.Run "'" & Workbooks("Name Manager.xla").FullName & "'!InitNameManager"

to:

Application.Run "'" & Workbooks("Name Manager 2007.xlam").FullName & "'!InitNameManager"

and the

Application.Run "'" & Workbooks("Name Manager.xla").FullName & "'!replacename", sOldname, sNewName, True

to:

Application.Run "'" & Workbooks("Name Manager 2007.xlam").FullName & "'!replacename", sOldname, sNewName, True

Works perfecto!

 

[Weaver]

Lol, just a case of dodgy filenames then.

Just for the record, here's a dodgy bit of code I wrote, with zero error checking

Sub test()
    For Each c In Selection
        oldname = c.Value
        newname = c.Offset(, 1).Value
        Range(oldname).Name = newname
        ActiveWorkbook.Names(oldname).Delete
    Next
End Sub

So long as you know what you're doing and there are no name conflicts, it appears to work, but since you got the nice version up and running, it's all good.