How to refer to a worksheet named range?

Question

Is there a way to refer to a worksheet named range without activating the sheet?

I have various sheets with ranges of the same name, and I would like to refer to the ranges in vba. I would expect something like Sheet2.Range("RangeName") to work, but that only works when Sheet2 is activated.

The only other way I can think of is through Range(Sheet2.Name & "!" & "RangeName"), but that only seems to work when Sheet2.Name doesn't contain spaces.

Do I need to stick to the activation of the sheets (which I like to prevent) or is there another way to refer to the range?

I am working with Excel 2016. Help is much appreciated.

Suggest recording a macro to do what you want then analyse the code generated in the macro. — SPlatten, Jan 25 '19 at 11:51
I would like to read the data in the range without activate or select. Recording doesn't give me that — Joost, Jan 25 '19 at 12:02
Possible duplicate of [Excel VBA: Workbook-scoped, worksheet dependent named formula/named range (result changes depending on the active worksheet)](https://stackoverflow.com/questions/22920090/excel-vba-workbook-scoped-worksheet-dependent-named-formula-named-range-resul) — Joost, Jan 26 '19 at 12:25

score 0 · Answer 1 · answered Jan 25 '19 at 11:49

0

Enclose the sheet name in single quotes, which allows the use of spaces.

Range("'" & Sheet2.Name & "'!" & "RangeName")

answered Jan 25 '19 at 11:49

jsheeran

2,912
2
17
32

score 0 · Answer 2 · answered Jan 25 '19 at 12:39

You could try:

Option Explicit

Sub test()

    Dim strSheetName As String, strrngName As String
    Dim ws As Worksheet
    Dim rng As Range, rngComplete As Range

    'Select sheet name
    strSheetName = "Sheet1"
    'Set sheet based on name selected above
    Set ws = ThisWorkbook.Worksheets(strSheetName)

    'Set range name
    strSheetName = "Test"
    'Set range based on sheet name & range name
    Set rng = ws.Range(strSheetName)

End Sub

Joost · Answer 3 · 2019-01-25T13:15:26.883

0

I am a bit puzzled. After more testing it seems that the obvious Sheet1.Range("RangeName") does seem to work, even when Sheet1 is not activated.

Thx for the answers. Sorry to have bothered you. I'll leave the question for reference

edited Jan 25 '19 at 13:15

answered Jan 25 '19 at 13:00

Joost

102
8

How to refer to a worksheet named range?

3 Answers3