Defined Names

strangerhere

New member
Joined
Jan 24, 2012
Messages
2
Reaction score
0
Points
0
Looking to create a macro for the following scenario:

Workbook1 contains Sheet labeled: New Input Sheet (copy from)
Workbook2 contains Sheet labeled: New Input Sheet (copy to)

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.. for example, Workbook1.New Input Sheet has a Defined Name = ProjectName with a value: Mobile Device Project

I would want to copy that value (Mobile Device Project) to Workbook2.New Input Sheet (into the cell with the Defined Name = ProjectName)
 

Simon Lloyd

Administrator
Joined
Apr 2, 2011
Messages
401
Reaction score
0
Points
0
Location
Manchester, England
Excel Version(s)
Excel 2016
Have you tried recording your actions and then taking a look at the code, it's a good place to start, have a go, post back with the code and we'll help you from there :)
 

strangerhere

New member
Joined
Jan 24, 2012
Messages
2
Reaction score
0
Points
0
I tried this code, but it is not copying over properly.. values are copying over to the incorrect cells.

Code:
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
 

Simon Lloyd

Administrator
Joined
Apr 2, 2011
Messages
401
Reaction score
0
Points
0
Location
Manchester, England
Excel Version(s)
Excel 2016
I tried this code, but it is not copying over properly.. values are copying over to the incorrect cells.

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
Remove the On Error Resume Next and then run it to see what erro it gives you, your trying to right a range to a range, ideally you should be writing like this
Code:
Workbooks("DestinationWorkbook.xls").Range(oneName.Name).Value = Workbooks("SourceWorkbook.xls").Range(oneName.Name).Value
but a better explanation and smaple workbooks would help a lot.
 
Top