0

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.

SSilk
  • 2,433
  • 7
  • 29
  • 44

0 Answers0