Results 1 to 4 of 4

Thread: Defined Names

  1. #1

    Defined Names



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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)

  2. #2
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    335
    Articles
    0
    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
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

  3. #3
    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

  4. #4
    Administrator Simon Lloyd's Avatar
    Join Date
    Apr 2011
    Location
    Manchester, England
    Posts
    335
    Articles
    0
    Quote Originally Posted by strangerhere View Post
    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.
    Kind regards,
    Simon Lloyd
    Microsoft Office Discussion

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •