VBA For copy Named Range (template) to another workbook

[MarcMermans]

Hello,

Is i't possible to copy named ranges from one workbook to an other workbook.

The first workbook contains a lot of named ranges and called template. The second workbook has the same structure but not all te named ranges are defined. With a VBA i would like to copy the template to the the second workbook and keep the data, but add the missing named ranges.

I have one template and hunderds of workbooks (xls) that must be modified. Beside that I have 100 different templates each with hunderdsd of workbooks.

Haven any one a idea to do this with a VBA procedure.


Thanks in advance,

Marc

 

[JoeMo]

Hello,

Is i't possible to copy named ranges from one workbook to an other workbook.

The first workbook contains a lot of named ranges and called template. The second workbook has the same structure but not all te named ranges are defined. With a VBA i would like to copy the template to the the second workbook and keep the data, but add the missing named ranges.

I have one template and hunderds of workbooks (xls) that must be modified. Beside that I have 100 different templates each with hunderdsd of workbooks.

Haven any one a idea to do this with a VBA procedure.


Thanks in advance,

Marc

This should replicate both local (sheet-level) and global (workbook-level) names and addresses from a "source" workbook to a "receiver" workbook provided the same sheets exist in both. The code goes into a standard module in the source workbook.

Sub ReplicateNamedRanges()
Dim Ws As Workbook 'The source wkbk
Dim Wr As Workbook 'The receiver wkbk
Dim nm As Name
Set Ws = ThisWorkbook
Set Wr = Workbooks("Name of receiver workbook & .file extension here")
For Each nm In Ws.Names
    nam = nm.Name
    adr = nm.RefersTo
    With Wr
        On Error Resume Next
        Names.Add nam, adr
        On Error GoTo 0
    End With
Next nm
End Sub

 

[Snakehips]

Marc,

Yes it is possible.

As a basic starting point for you, if you have your 'template' workbook open and run the following code from another open workbook it should copy across the named ranges form template.

Sub GetNames()
Set wbTemp = Workbooks("template")
For Each Name In wbTemp.Names
ThisWorkbook.Names.Add Name:=Name.Name, RefersTo:=Name   'Assumes all to be Workbook Scope
Next Name
End Sub

Given the scale of your template and workbook numbers you will probably want to further automate the process.

Hope that helps.