Copy Named Ranges

[prasaddn]

Hi,


In reference to topic http://chandoo.org/forums/topic/how-to-extract-name-ranges-to-excel-sheet,


I would like to know how can we smartly/easily copy all the named ranges from one workbook to another workbook with references to sheets in new workbook?


Regards,

Prasad DN

PS: pls dont suggest use "save as" of current workbook

 

[Luke M]

I'd probably paste the list somewhere and then "Create from selection" in the new workbook.


In 2007, you can paste the list of named ranges via

Formula - Use in Formula, Paste Names, Paste List

 

[prasaddn]

I tried your method, it does create the name range as i select left col (in "create from selection") pop up, but the references are only referring to particular cell and not the formula or what is inside that cell.


Any more suggestions


Regards,

Prasad DN

PS: I am just trying to learn so that I may use this at later stage.

 

[Hui]

Prasad


Yes,


Use the technique I described in the post above to save the Named Formula and the Formulas top some cells


Copy the Names and Formulas to a new workbook using Copy/Paste


Then use the Load_Named_Ranges technique I have described in: http://chandoo.org/wp/2011/06/23/automating-repetitive-tasks/

 

[SirJB7]

Hi, prasaddn!


Sorry for arriving late... long weekend


Try this code from within the new workbook:

-----

[pre]

Option Explicit
Sub DuplicateRanges()
Dim I As Integer
With Workbooks("OldWorkbook.xlsx")
For I = 1 To .Names.Count
ActiveWorkbook.Names.Add .Names(I).Name, .Names(I).RefersTo
Next I
End With
End Sub

[/pre]
-----

Regards!