Renaming Named Ranges with VBA

[AwooOOoo]

Hi,
I have a template sheet that contains a number of named ranges that I want to make unique when I copy the sheet, but I'm having trouble with it and could use some help. Let me explain

The Template Sheet contains named ranges like
xxx_var1
xxx_thingy2

I have a function to copy the sheet and I want to rename the ranges so that; (copy TemplateSheet to Copy1)
xxx_var1 ===> Copy1_var1
xxx_thingy2 ===> Copy1_thingy2

That way I can uniquely reference any of these named ranged from a summary sheet with references like;
'Copy1'!Copy1_var1
'Copy2'!Copy2_var1
'Copy3'!Copy3_var1

The copy works fine, but it is the rename I'm having trouble with. I've referred to this existing post (http://www.mrexcel.com/forum/excel-...e-ranges-using-visual-basic-applications.html), but am still having trouble.

When I rename the ranges they all get a '[Sheet Name]'! prefix which is then hard-coded into the cell. This means that the cell name for example is "'Copy1'!Copy1_var1" and to reference it I would have to do something like "'Copy1'!'Copy1'!Copy1_var1" which doesn't work.

Below is a snippet of my rename code. s1 correctly removes the xxx prefix. s2 seems to fail such that Nm.Name ends up being named "'Copy1'!xxx_var1" which makes no sense as I've already removed the xxx prefix. I have a second s2 commented out which replaces the Sheet reference with a hard string "Fluffy" and this works such that a resulting range is "Fluffy_var1" and can be referenced with "'Fluffy'!Fluffy_var1".

        'Rename the named ranges (variables) on the template sheet so they are unique for the new sheet
        Dim Nm As Name
        For Each Nm In ActiveSheet.Names
            If (UBound(Split(Nm, "!")) > 0) Then 'Only process names local to the worksheet
                s1 = WorksheetFunction.Substitute(Nm.Name, "xxx", "")                                       ' Remove the xxx generic prefix
                s2 = WorksheetFunction.Substitute(s1, "'" & ProjectName & "'!", ProjectName)    ' This doesn't
                's2 = WorksheetFunction.Substitute(s1, "'" & ProjectName & "'!", "Fluffy")       ' This works?
                Nm.Name = s2
            End If
        Next Nm

Any insights into what is going on and how to fix it would be appreciated.

Sincerely, Paul.

 

[lexxie2013]

Hi Paul,

Just to be clear, is the active sheet the copied sheet or the template ?

 

[AwooOOoo]

Hi lexxie,
The newly created sheet is the active sheet. Here is the snippet that precedes the last snippet. It does the copy and sets the activesheet

        'Create a New Project Sheet
        If (ComboBox1.Value = "(move to end)") Then
            Sheets("Project Template").Copy after:=Sheets(Sheets.Count - 1)
        Else
            Sheets("Project Template").Copy after:=Sheets(ComboBox1.Value)
        End If
        
        ActiveSheet.Name = ShtName

Sincerely, Paul.

 

[lexxie2013]

Just a thought, but what happens if you replace your variable settings for s1 and s2 as follows :

<code>
s1 = WorksheetFunction.Substitute(Nm.Name, ActiveSheet.Name & "!xxx", "")
s2 = WorksheetFunction.Substitute(s1, "_", ProjectName & "_")
<code>

 

[lexxie2013]

Sorry Paul, on the second line, there should have been an underscore between both sets of quotes.

 

[AwooOOoo]

The underscores are actually there, you just can't see them (they came across with the copy).

Your s1 row fails to detect a match as you're not checking for the ' on either side of the Active Sheet. This follow correct this (but then you have the same problem as me

s1 = WorksheetFunction.Substitute(Nm.Name, "'" & ActiveSheet.Name & "'!xxx", "")   'updated this line
            s2 = WorksheetFunction.Substitute(s1, "_", ProjectName & "_")

The Nm.Name = s2 is what fails. s2 holds the correct value but after stepping through the line Nm.Name is its original value 'Copy1'!xxx_var1

Wierd? Why would the assignment fail?

Paul

 

[Cool blue]

Hi, you don't need a different name, it is already unique because it's qualified by the sheet name. You just reference like

Workbooks(wbName).Names("xxx_var1")

If the name has workbook scope or

Workbooks(wbName).Sheets(shName).Names("xxx_var1")

Where wbName and shName are string variables created in your code.

 

[AwooOOoo]

Hmmm,
That might be the solution. I thought there might be a problem with the named ranges being of the same name, but I guess they are isolated at a higher level with the sheet name,

 

[Cool blue]

Yes, you can only reference without sheet or workbook qualification in formulae in the workbook that they are in, any reference to an external name must be qualified. If you a referencing the names in VBA, then you should always fully qualify the names to reduce the risk of errors or confusion when maintaining your code.

Adding different names for the same property in different workbooks is unnecessary and confusing.