Export Defined Name Values and Paste to Another Workbook

[strangerhere]

Greetings
I am trying to find the easiest way to:
1. Copy values from all Defined Names in a Worksheet
2. Paste those values into a different Workbook (Worksheet) where the Defined Names match.

I have this code so far (pasted partial), that does work, but required manually retrieving each Defined Name. I'm searching for a better way to do this.. perhaps a loop.. but not sure.

Sub Macro1()

Dim SourceFile As String
Dim wb As Workbook
Set wb = Workbooks.Open(Application.GetOpenFilename)


wb.Activate
Sheets("New Input Sheet").Select
SourceFile = Range("ProjectName", "GTPO").Select
Selection.Copy
Windows("ARC_RRC Calculator V10.1.xls").Activate
Range("ProjectName", "GTPO").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

wb.Activate
Sheets("New Input Sheet").Select
SourceFile = Range("Srm:OtherInputName").Select
Selection.Copy
Windows("ARC_RRC Calculator V10.1.xls").Activate
Range("Srm:OtherInputName").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

 

[mikerickson]

Dim oneName as Name

On Error Resume Next
For each oneName in ThisWorkbook.Names
    
    Workbooks("Other.xls").Names(oneName.Name).RefersToRange.Value = oneName.RefersToRange.Value
     
Next oneName
On Error Goto 0

 

[strangerhere]

Wow! Thank you for such a quick response!

I got this to work on an excel Workbook that I created.. but (I should have explained this).. the Workbook I want to copy the defined names from is on a specificly named Worksheet and the Workbook I want to copy to is also on a specificly name Worksheet.. The worksheets are labeled the same (New Input Sheet).

Also, not sure if this matters, but some of the cells in the Worksheet are protected, but the ones with the defined names are not.

Thanks

 

[mikerickson]

If the names are scoped only for one worksheet, have oneName loop through the Workbook("one.xls").Worksheets("New Input Sheet").Names

otherwise, putting in a test for oneName.RefersToRange.Parent.Name = "New Input Sheet" should tame things down.

 

[strangerhere]

Greetings
Unfortunately this code isn't working as hoping. It seems like it only copies some of the values over to the cells with the same name. If there is anything else I can provide to assist with this, kindly let me know.
Thanks

 

[mikerickson]

Since I'm not clear about what the desired result is, I'm not sure what the problem is.
Could you post an example.

 

[strangerhere]

Sure..

Workbook1 contains Sheet labeled: New Input Sheet
Workbook2 contains Sheet labeled: New Input Sheet

I want to copy each value related to the Defined Name from Workbook1.New Input Sheet that matches the Defined Name in Workbook2.New Input Sheet

So, Workbook1.New Input Sheet has a Defined Name = ProjectName with a value: Mobile Device Project

I would want to copy that value to Workbook2.New Input Sheet (under the cell with the Defined Name = ProjectName

Hope this helps.. Thx!

 

[mikerickson]

Is DefinedName a Named constant or a Named Range?

 

[strangerhere]

The Defined Name is constant