Solved: Bulk Insert of Names for Ranges/Cells?

[RonMcK3]

I have a worksheet with 17 Names (cells or ranges); I want to build 4 additional identical worksheets each with its own unique set of those 17 names.

Is it possible to do a bulk Name addition where a unique character or string (such as the sheet number) is added to each name for the sheet they are being applied to?

Thanks,

[will1128]

Ron,

Are the ranges to be the same on each worksheet and the worksheets unique?

Or are the ranges and the worksheets meant to be unique?

[RonMcK]

Will,

Initially, the ranges can be identical, the named cells will be identical. After typing my request, it dawned on me that I could figure it out by recording a macro; so, I did that:[vba]Sub Macro1()
'
' Macro1 Macro
'

'
ActiveWorkbook.Names.Add Name:="FolioNo1", RefersToR1C1:= _
"='Sample Control Sheet'!R4C1:R50C1"
ActiveWorkbook.Names("FolioNo1").Comment = ""
End Sub[/vba]
I just need to write a small VBA program that iterates through the list of cells and ranges changing the sheet name (here 'Sample Control Sheet') and changing the number from 1 to 2, 3, 4, or 5, as needed. Finally, I can skip the Comment line since I'm not using it. This will then populate the Names file with the required information.

Will this work? Is there an easier or more elegant way to do this?

Thanks,

[geekgirlau]

You actually don't need to have an incrementing number on the range name - you can make the range specific to that sheet, and all the ranges will therefore share the same names.

[VBA]Sub CreateNames()
Dim ws As Worksheet
Dim nm As Name
Dim strRange() As String
Dim i As Long


' capture a list of those range names referring to the control sheet
' assumes you want every range
For Each nm In ActiveWorkbook.Names
If Left(nm.RefersTo, 24) = "='Sample Control Sheet'!" Then
ReDim Preserve strRange(0 To 1, 0 To i)

strRange(0, i) = nm.Name
strRange(1, i) = nm.RefersTo
i = i + 1
End If
Next nm

' add those range names to all remaining sheets, but refer to the same cell addresses
' on the current sheet (in place of the control sheet)
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> shControl.Name Then
For i = 0 To UBound(strRange, 2)
ws.Names.Add strRange(0, i), Replace(strRange(1, i), "Sample Control Sheet", ws.Name)
Next i
End If
Next ws
End Sub
[/VBA]

[RonMcK]

Ah! Tres elegant!

Thank you very much, GeekGirlAU. I'll give this a spin, I like this much more than the brute force solution I had conjured up.

Have a wonderful day! Thanks, again.

[RonMcK]

I seem to be having some challenges getting this to work. I've tried it on both my PC (XL 2007) and Mac (XL 2004); I've also made a stab at fixing it but have not yet been successful.

Thanks in advance (one and all) for your assistance.

Here's what we have:
[vba]Sub CreateNames()
Dim ws As Worksheet
Dim nm As Name
Dim strRange() As String
Dim i As Long
Dim shControl As Worksheet ' added to define

' capture a list of those range names referring to the control sheet
' assumes you want every range
For Each nm In ActiveWorkbook.Names
If Left(nm.RefersTo, 24) = "='Sample Control Sheet'!" Then

ReDim Preserve strRange(0 To 1, 0 To i)

strRange(0, i) = nm.Name
Debug.Print nm.Name
strRange(1, i) = nm.RefersTo
i = i + 1
End If
Next nm

' add those range names to all remaining sheets, but refer to the same cell addresses
' on the current sheet (in place of the control sheet)
For Each ws In ActiveWorkbook.Worksheets
Debug.Print ActiveSheet.Name
' the next line reported error 424, on first run
' followed by reporting error 91, after adding DIM
If ws.Name <> shControl.Name Then
For i = 0 To UBound(strRange, 2)
' when running on Mac XL2004, get Compile error: sub or function
' not defined, clicking OK button reveals REPLACE is highlighted
ws.Names.Add strRange(0, i), Replace(strRange(1, i), "Sample Control Sheet", ws.Name)
Next i
End If
Next ws
End Sub
[/vba]

Thanks!

[RonMcK]

Hi, All,

Well, it took some persistent debugging but here is the working program. In the process, I discovered that there are some Names in the worksheet which are prefixed with the sheet name, in addition to the RefersTo values which are all prefixed that way. This added some IF-THEN-ELSE code in the second FOR EACH-NEXT loop for writing the names for the additional worksheets.

Here is the code I settled on and beneath it is part of the debug data, the key to needing to also test Names for the sheet name prefix.

A very big THANK YOU to GeekGirlAU for getting me headed in the right direction.

[vba]Sub CreateNames()
Dim ws As Worksheet
Dim nm As Name
Dim strRange() As String
Dim i As Long
Dim SrcSheet As String

SrcSheet = "Grade 1"

' capture a list of those range names referring to the SrcSheet
' assumes you want every range (Yes, I do!)
For Each nm In ActiveWorkbook.Names
If Left(nm.RefersTo, Len("='" & SrcSheet & "'!")) = "='" & SrcSheet & "'!" Then

ReDim Preserve strRange(0 To 1, 0 To i)
strRange(0, i) = nm.Name
strRange(1, i) = nm.RefersTo
i = i + 1
End If
Next nm
' add those range names to all remaining sheets, but refer to the same cell addresses
' on the current sheet (in place of the control sheet)
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> SrcSheet And ws.Name <> "Instructions for Use" Then ' was: shControl.Name

For i = 0 To UBound(strRange, 2)

If Left(strRange(0, i), Len("='" & SrcSheet & "'!")) = "='" & SrcSheet & "'!" Then

ws.Names.Add strRange(0, i), Replace(strRange(1, i), SrcSheet, ws.Name)
Else
ws.Names.Add Replace(strRange(0, i), SrcSheet, ws.Name), Replace(strRange(1, i), SrcSheet, ws.Name)
End If

Next i
End If
Next ws
End Sub
[/vba]


The debug data for iterations 8 and 9 are as follows:

 8 
              PgStatus      ='Grade 1'!$F$4:$F$439      Grade 1       Grade 2
 9 
              'Grade 1'!Print_Area        ='Grade 1'!$A$1:$V$439      Grade 1       Grade 2

Thanks, all !

All y'all on this side of the Pond, have a restful Labor Day weekend!

[Jan Karel Pieterse]

For your information:
Excel range names have a scope.
1. By default, a range name has workbook scope (global names). Each global name can only exist once.
2. They can be local to a worksheet (in which case you'll see the worksheets name after them in the Insert, name dialog. Also, names local to a sheet are not visible if you're on another worksheet).
You define a local name by putting the sheetname in front of the name:
'Sheet name'!RangeName

If you copy a worksheet which has global range names pointing to any of it's cells, the copy of the sheet will have local copies of those global range names. This may lead to confusion in your model, because those range names -when used on the copied sheet- point to the cells on the copy, not to the global range names!
Therefore, duplicate global/local range names are a spreadsheet disaster in disguise.

Download my Name Manager, it will make your life much, much easier.

www.jkp-ads.com/officemarketplacenm-en.asp

[RonMcK]

Thanks for the reminder, Jan Karel.

[mikerickson]

The relative sheet format might work for you.

Name: myName
RefersTo: =!$A$1:$B$2


myName will refer to a range on the same sheet as the formula that uses the name.

And any sheets that are inserted will have a range named myName.

[RonMcK]

Mike (or is it Mik?),

I'm learning that I have a bunch to learn about Names and multiple worksheets.

Thanks,

[mikerickson]

Its known that that syntax can be quirky. Glad I could help.

[Jan Karel Pieterse]

I'd advise you to never use that relative format as it is buggy. As soon as ANY VBA action fires a calculation, all formulas which use that name will pull their value from the ACTIVE sheet!