Is there a way to access a named range in a specific workbook in Excel VBA?
Basically I'm trying to do something like this...
Sub test()
Dim wb As Workbook
Dim rng As Range
Set wb = ActiveWorkbook
Set rng = wb.Range("tbl") ' Error: "Object doesn't support this property or method"
Debug.Print rng.Address
End Sub
...and the 5th line is giving me the error shown.
If I instead activate wb
first, then access that Range without qualifying it with the workbook handle, this works:
Sub test()
Dim wb As Workbook
Dim rng As Range
Set wb = ActiveWorkbook
wb.Activate
Set rng = Range("tbl")
Debug.Print rng.Address
End Sub
Is there a way to make the first method work? I prefer not to have to activate the other sheet first as it feels clunky.
Thanks.