Get Named Range Values

1
votes

I have two workbooks that have a lot of the same named ranges and I need to move values from the second workbook to the first. I can do this by specifying which sheet the range is on and the name of range, but there are about 200 named ranges spread out over 20-something sheets and I only need the named ranges that are on 11 of those sheets. I could take the time to figure it out and hard code moving each one, but would really like it to be a mostly automated process. That way if a new range is added it can just be done automatically and I don't have to update the code.

The code I have so far is:

For Each Name In sheetList
    Set sheet2vals = sheet2.Sheets(Name).Range(rangename)
    Set sheet1vals = sheet1.Sheets(Name).Range(rangename)
    sheet2vals.Copy sheet1vals
Next Name

sheetList is an array of sheet names where all the ranges are stored. That part works exactly as I need it, I just need to find a way to pull the ranges from each of the sheets and move them. However, the only way I can seem to find to get the list of named ranges is by pulling at the Workbook level, which doesn't tell me what sheet it is on.

1
excelvba
How do the 11 sheets (which are the targeted ones) differ from the others? You will have to set up a loop to automate this process. Therefor we need some conditions. - Jonas
stackoverflow.com/questions/40459845/how-do-i-use-referstorange - BigBen
Each Name has a RefersToRange property that gives you a Range object, and every Range has a Parent property that gives you the Worksheet it belongs to. - Mathieu Guindon
@MathieuGuindon this is exactly what I needed. If you want to move this to a response I will mark it as accepted. - John Carty

1 Answers

1
votes

Each Name has a RefersToRange property that gives you a Range object, and every Range has a Parent property that gives you the Worksheet it belongs to.

So if you have a Name object, you can always know what sheet it's referring to.