Copy named range contents and range definition from one workbook to another

[dhregan]

I have two workbooks. Workbook_A is a "shell" with the following named ranges and definitions:

Name: Parameter_1, Refers To: Sheet1!$A$11
Name: Parameter_2, Refers To: Sheet1!$A$12
Name: Parameter_3, Refers To: Sheet1!$A$13

Workbook_B - which was originally based on the shell - has the following named ranges and definitions:

Name: Parameter_1, Refers To: Sheet1!$A$11
Name: Parameter_2, Refers To: Sheet1!$A$12
Name: Parameter_3, Refers To: Sheet1!$A$13
Name: Parameter_4, Refers To: Sheet1!$A$14
Name: Parameter_5, Refers To: Sheet1!$A$15

I am trying to write a macro that will copy the contents and formats of the named ranges - as well as the named range definitions - from Workbook_B to Workbook_A. That is, when the macro is done running, Workbook_A will have the following named ranges and definitions:

Name: Parameter_1, Refers To: Sheet1!$A$11
Name: Parameter_2, Refers To: Sheet1!$A$12
Name: Parameter_3, Refers To: Sheet1!$A$13
Name: Parameter_4, Refers To: Sheet1!$A$14
Name: Parameter_5, Refers To: Sheet1!$A$15

I know how to copy the contents and formats, but I can't seem to get the named range definitions over. How can this be done? Is it possible to copy contents, formats and named range definition in one statement?

 

[Yongle]

Try this method to copy the names and RefersTo

with the 2 workbooks set to wbA and wbB

  Dim n As Integer, nm As String
  For n = 1 To 5
      nm = "Parameter_" & n
      On Error Resume Next
      wbA.Names(nm).Delete
      wbA.Names.Add Name:=nm, RefersTo:=wbB.Names("Parameter_" & n).RefersTo
  Next

 

[Yongle]

Opps forgot to incude the line to copy the contents and the formats

insert this in the line below the one creating the names

wbB.Names(nm).RefersToRange.Cells.Copy wbA.Names(nm).RefersToRange.Cells

 

[dhregan]

Thanks Yongle. Because I won't necessarily know the fixed number of named ranges in Workbook_B, is there a way to copy without relying on knowing how many occurrences of the "Parameter_#" structure are present in Workbook_B? In other words, can the macro be constructed to make the copy based on a dynamic number of occurrences in Workbook_B?

If it helps, I also have a "comprehensive" named range to capture all of the named items in Workbook_B. That is, I have another named range in Workbook_B called "Range Inventory" which spans cells $A$11:$A$15. So, Range Inventory will always contain the cells with the named ranges that I want to copy from Workbook_B to Workbook_A.