apply all names

[gelu.tudose]

Hi there,

Objective: - raplace cell references with names

Question: - Is it possible to apply all the defined names without
having to select them one by one in the Apply Names dialog box?

Thank you

 

[NickH]

Here's something to get you started...

Sub NamesLoop()
Dim N As Name
Dim strIN As String
Dim strOUT As String

For Each N In ActiveWorkbook.Names
strIN = N.RefersTo

'----------------------------------------------------------
' strIN will be in a form something like -> "=Sheet1!R1C1"

' Here you need to parse strIN, make your change and put _
the result into strOUT
'----------------------------------------------------------

N.RefersToR1C1 = strOUT
Next N
End Sub

HTH NickH

 

[djrichard]

'----------------------------------------------------------
' strIN will be in a form something like -> "=Sheet1!R1C1"

' Here you need to parse strIN, make your change and put _
the result into strOUT
'----------------------------------------------------------

Nick, the code you suggested is for changing what the names refer to. I think the original request was for applying the names, not changing what they refer to.

I've come across two bits of code for applying all names, the first of which doesn't execute (although Microsoft suggests it in an example in their help file - go figure), and the second of which partially executes, but stops as soon as there's a name that can not be applied to the sheet in question.

Sub ApplyNamesAll()
 
	Cells.ApplyNames
 
End Sub

Sub ApplyNamesAll2()
 
	Dim Nm As Name
	For Each Nm In ThisWorkbook.Names
		ActiveSheet.Cells.ApplyNames Names:=Nm.Name
	Next Nm
 
End Sub