Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    I am struggling to get the exact syntax correct for this simple task !

    I want to set a single cell, using its defined NAME, to the value of a NAMED cell in another workbook.

    Can you guide me please ?

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Sorry, should have said:

    This works (ie copying within the active workbook): Worksheets("Data").Range("Target") = Range("Input").Value

    But this doesn't (ie copying from another (open) workbook in the same folder): Worksheets("Data").Range("Target") = Workbooks("Trustnet Portfolio.xls").Range("Input").Value

    Thanks

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    But this doesn't (ie copying from another (open) workbook in the same folder): Worksheets("Data").Range("Target") = Workbooks("Trustnet Portfolio.xls").Range("Input").Value
    You still need to specify the worksheet on the Source workbook

    Worksheets("Data").Range("Target") = Workbooks("Trustnet Portfolio.xls").Worksheets("SheetTheNamedRangeIsOn ").Range("Input").Value

    Assuming that Worksheets("Data").Range("Target") is in the Active Workbook
    Andrew

  4. #4
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Thank you - that does indeed work . . . I had mistakenly thought that Cell Names were independent of the specific Worksheet in a Workbook.

    They do seem to be independent in the target Workbook (ie I do not have to specify the Worksheet where the named cell exists in order to set its value) and I am wondering if there is a syntax which finds the named cell's value in the source Workbook without having to specify the Worksheet.

    I ask for all the reasons one uses named cells in the first place - if I edit the source Worksheet I don't want to have to re-write the VB - if possible.

  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    You can get the RefersTo part of the name by using

    strRef=workbooks("NameOfWorkbook").Names("NameOfRa nge")

    Then strRef would have something like ='SheetName'!CellRef

    From that you could find the Sheet name using some string functions like instr and mid

    You could also strip off any ' that exist using replace

    so strref=mid(strref,2) will lose the =
    strref=replace(strref, "'", "") will lose the single ' if there are any
    Then all you need is to find the !
    so

    strSheet=left(strref,instr(strref,"!")-1)


    Might well be a better way though. Probably is.
    Andrew

  6. #6
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    It is going to be easier to keep all the named cells on the same sheet in the source workbook - you've stopped me wasting time in writing VB which is more complex than doing it the simple way !

    Martin

Posting Permissions

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