Getting a value from a named range in another workbook

Question

I am trying to copy a value from a named range in one workbook into a named range in another workbook. I am hitting an issue with getting the value from the other workbook. I am using the code:

i = 0

sWorkBookB = Range("BaseFileName").Value
sWorkSheetB = Range("HCSheetName").Value
sNewValue = Workbooks(sWorkBookB).Worksheets(sWorkSheetB).Range("Value1").Offset(i, 0).Value

This is returning a blank in the immediate window but the Value1 named range is a cell with the word "Fund" in it.

Any help would be apricated.

Note that if i = 0, then .Offset(i, 0) is redundant. What other debugging have you done, e.g. using Debug.Print Workbooks(sWorkBookB).Worksheets(sWorkSheetB).Range("Value1").Address? — BigBen
Are you sure the file and worksheet names are fine? Have you debugged it step by step? — SnowGroomer
@BigBen the i=0 and the offset are so I can loop through once I have it working. The named range is a cell at the top of a list and once I get it working for that cell I will loop through the others. — anon
@SnowGroomer yes I have debugged each step. it is the Value1 named range that is not working. — anon

RLee RLee · Accepted Answer · 2021-07-13T23:44:40

I would set the named ranges in each WB to be dynamic using the OFFSET function first. Then, whenever you need to update the range, you can simply clear the range in the destination file, copy the range from the source file, and paste it in the destination file. That way, you don't have to do one-off edits. You can add/delete/edit the range in the source as desired and then just overwrite the range in destination.

Getting a value from a named range in another workbook

1 Answers