Range question

[greg]

This should be an easy question

If I have multiple workbooks open.
Each workbook is assigned to a var. such as
WB1, WB2, etc...

If I have a name range. Such as "NameRange1"
How do I get the values from each workbook. Without knowing the worksheet.
Range("NameRange1") seems to get only the active workbook.
There does not seem to be a WB1.Range method. On the workbook.
Can you do this without Activate on each workbook?

thanks

 

[Sam Wilson]

dim wb as workbook
dim ws as worksheet

for each wb in workbooks
for each ws in wb.worksheets
ws.range("NameRange1")...
next ws
next wb

 

[greg]

Ah,
You have to go through the worksheet.
Even though a named range is unique to the workbook.

 

[JLGWhiz]

Not sure what you are after, but this will return the sheet and cell
reference for a range named "test"


Sub dk()
MsgBox ThisWorkbook.Names("test").RefersTo
End Sub

 

[Patrick Molloy]

code sample
Option Explicit
Sub check_range()
Dim nm As Name
Dim cell As Range
Dim wb As Workbook
For Each wb In Workbooks
Set cell = wb.Names.Item("range1").RefersToRange

'do something
cell.value = format$(now,"HH:MM:SS")

'don't need these three - these are just for demo
wb.Activate
cell.Parent.Activate
cell.Select
Next
End Sub


once your range object 'cell' is set to the range, you can utilize it
without it, or even the workbook being active