Get NamedRange from unknown worksheet in other workbook

0
votes

I am trying to extract data from a standardized excel form where all the values I want are namedRanges. The NamedRanges are worksheet specfic, e.g. c_001 referes to Sheet1!$X$38.

I have tried retrieving all Names in the workbook, e.g.

'Runtime error 13 - Type mismatch
Set namesInWb = Workbooks(wbName).Names

This does not seem to work though, possibly since all the names are worksheet specific?

I then thought I could get the namne through the Range object, e.g. I want to be able to use something like this to get the wsName:

tempRange.Worksheet.Name

I need to retrieve the Range object first though, but nothing I can think of works without knowing the worksheet name.

'Not working - Runtime error 438 - Object does not support this property or method
Set tempRange = Workbooks(wbName).Range("c_001")


'This works now, only failed due to another error earlier in the code that I had missed
Set tempRange = Range(currentName)

UPDATE: Last method works now. Only failed due to errors earlier in the code which I had missed.

2
excelvbanamed-ranges
Have you tried looping through all of the sheets to find the specific named range? – RGA
What are the errors from the above/ – Nathan_Sav
The Names collection returns all names in the active book. Can you explain in what way this does not work? Do you get an error message? Sorry @Nathan_Sav; I tried to upvote your comment, but I then I accidentally downvoted it. SO won't let me re-upvote. – David Rushton
@Nathan_Sav I have updated with the error messages I get. – johankr
From my perspective Workbooks(wbName).Names ought to work, but maybe it returns Nothing as there are no global Names? I am unsure if and how Excel differentiates been worksheet specific names and those who are not. – johankr

2 Answers

0
votes

In VBA you can also refer to named ranges by putting them in square brackets like this

Dim namedRange as Range
Set namedRange = [rangeName]

Or simply call its methods directly...

[rangeName].Select

0
votes

To select a range we should be knowing the worksheet name. Range is the method of worksheet, so we cannot apply the Range method on workbook.

If we do not know the sheet names under the workbook then you can use the index of worksheets as below

ThisWorkbook.Worksheets(1).Range("A1").Value

And you can also able to know the name of the sheet by using (If you require for multiple operation on the same workbook)

ThisWorkbook.Worksheets(1).Name

where ThisWorkbok will be object of your workbook.